SQL RNNR

1 DBA's Professional Blog

Categories: Scripts

A Trio of Functions

by Jason Brimhall

Categories: News, Professional, Scripts, SSC

Tags: Scripts, Security, TSQL

Comments: No Comments

Published on: January 17, 2012

I found myself perusing an execution plan the other day. I know, big surprise there. This execution plan showed me some interesting things I had never really paid much attention to in the past. When I started paying attention to these things, I found myself jumping down a rabbit hole.

It all started with a bit of curiosity to see if I could make an “admin” script perform a bit better. The execution plans started showing some table valued functions that I knew I hadn’t included in the query. Subsequently, I found myself wondering – what is that?

The items that made me curious were all table valued functions. There were three of them (different) in this particular plan. I started looking hither and thither to find these functions. It didn’t take long to figure out that I could find them in the mssqlsystemresource database. So I proceeded to making a copy of the database and attaching a copy of it for further learning opportunities.

The three functions are:

SYSSESSIONS

FNGETSQL

SYSCONNECTIONS

Knowing the query and based on these names, I began looking in the appropriate DMOs to see what I could find. Here are the scripts for each of those DMO’s.

Code block

USE [mssqlsystemresource_test]
GO
 
/****** Object:  View [sys].[dm_exec_connections]    Script Date: 01/16/2012 22:39:32 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE VIEW [sys].[dm_exec_connections] AS
	SELECT *
	FROM OPENROWSET(TABLE SYSCONNECTIONS)
 
GO
 
/****** Object:  View [sys].[dm_exec_sessions]    Script Date: 01/16/2012 22:39:37 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE VIEW [sys].[dm_exec_sessions] AS
	SELECT *
	FROM OPENROWSET(TABLE SYSSESSIONS)
 
GO
 
/****** Object:  UserDefinedFunction [sys].[dm_exec_sql_text]    Script Date: 01/16/2012 22:39:55 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE FUNCTION [sys].[dm_exec_sql_text](@handle VARBINARY(64))
RETURNS TABLE
AS
	RETURN SELECT * FROM OPENROWSET(TABLE FNGETSQL, @handle)
 
GO

Cool. I can now see the internals of each of the DMOs – sort of. You see, there is an OPENROWSET call in each of these objects. Each call uses an undocumented feature called TABLE. This is an internal command used by the engine and you won’t find much on it (mostly people asking what it is and Microsoft saying they won’t tell).

Here is the fun part. If you try to run that code outside of querying the DMO, you will receive error messages. If you try to create a new view utilizing the Openrowset, it will fail. It is reserved for internal usage. With that said, just continue to use the DMO and you will be fine. Personally, I was curious to find out how it worked so I tried a bit to find it.

So there you have it. If you are curious what is the internal makings of these DMOs, you can script them from the resource database. Alternatively, you could also run sp_helptext. I like to check these things from the resource database. It feels more like an adventure. Have fun with it and see what you will learn.

Database Data and Log Size Info

by Jason Brimhall

Categories: News, Professional, Scripts, SSC

Tags: Scripts, TSQL

Comments: 5 Comments

Published on: December 5, 2011

Have you ever wanted or needed to get the data and log file size for all of the databases in your server?

I have seen scripts that would get this information in the past. All of the scripts seemed to use either a cursor or the proc from Microsoft called sp_MSforeachdb. Having seen some of these scripts, I wondered if there might be a different way of doing it (that was also different than you see in my TableSpace Scripts).

After putting some thought into it, I decided on a plan of attack and went to work on building a query that would do the trick. But before I continue, I will advise that running a script like this on large databases may take a while to execute.

Keeping these things in mind (potential slowness when run on large db and wanting to try something different), I came up with this method (tested on Instances with small databases as well as Instances with 2TB Databases).

Code block

DECLARE @MaxDataSize	INT
SELECT @MaxDataSize = MAX(d.cntr_value)/1024/1024 --Convert to GB
    FROM sys.dm_os_performance_counters d
    WHERE d.OBJECT_NAME like '%databases%'
       and d.counter_name = 'Data File(s) Size (KB)'
    GROUP BY d.counter_name,d.OBJECT_NAME
 
DECLARE
@DbSize TABLE (Database_Id INT, DataUsedMB FLOAT)
 
IF @MaxDataSize > 250 --If Largest DB is >250GB then use forEachdb, else use Physical Index Stats
BEGIN
	INSERT INTO @DbSize (Database_Id,DataUsedMB)
	EXEC sp_MSforeachdb
	'select db_id(''?'') , (SUM(ps.reserved_page_count)*8)/1024 from ?.sys.dm_db_partition_stats ps'; 
 
	SELECT
        DB_ID(instance_name) Database_Id
        , instance_name Database_Name
        ,CAST(pvt.[DATA FILE(s) SIZE (KB)] AS FLOAT)/1024 AS DataFileSizeMB
        ,isnull(ca.DataUsedMB,.1) AS DataUsedMB
        ,(CAST(pvt.[DATA FILE(s) SIZE (KB)] AS FLOAT)/1024) - isnull(ca.DataUsedMB,.1) AS DataFreeMB
        , CASE WHEN pvt.[DATA FILE(s) SIZE (KB)] = 0
			THEN 0
			ELSE ROUND((((CAST(pvt.[DATA FILE(s) SIZE (KB)] AS FLOAT)/1024) - isnull(ca.DataUsedMB,.1)) / (CAST(pvt.[DATA FILE(s) SIZE (KB)] AS FLOAT)/1024))*100, 2)
			END DataFreePercent
        ,CAST(pvt.[LOG FILE(s) SIZE (KB)] AS FLOAT)/1024 AS LogFileSizeMB
        ,CAST(pvt.[LOG FILE(s) Used SIZE (KB)] AS FLOAT)/1024 AS LogFileUsedMB
        ,(CAST(pvt.[LOG FILE(s) SIZE (KB)] AS FLOAT) - pvt.[LOG FILE(s) Used SIZE (KB)])/1024 LogFreeSizeMB
        ,pvt.[PERCENT LOG Used] AS LogUsedPercent
        ,100-pvt.[PERCENT LOG Used] AS LogFreePercent
    FROM sys.dm_os_performance_counters d
    PIVOT (SUM(cntr_value) FOR counter_name IN ([DATA FILE(s) SIZE (KB)],[LOG FILE(s) SIZE (KB)],[LOG FILE(s) Used SIZE (KB)],[PERCENT LOG Used])) pvt
    LEFT Outer Join (SELECT Database_Id,Null AS page_count,DataUsedMB FROM @DbSize) CA
		ON (ca.database_id = DB_ID(instance_name))
    WHERE OBJECT_NAME like '%databases%'
		and pvt.[DATA FILE(s) SIZE (KB)] IS not null
		And pvt.[LOG FILE(s) SIZE (KB)] IS not null
		And pvt.[LOG FILE(s) Used SIZE (KB)] IS not null
		And pvt.[PERCENT LOG Used] IS not null
		and instance_name <> '_Total'
    ORDER BY Database_ID
END
ELSE
BEGIN
SELECT
        DB_ID(instance_name) Database_Id
        , instance_name Database_Name
        ,CAST(pvt.[DATA FILE(s) SIZE (KB)] AS FLOAT)/1024 AS DataFileSizeMB
        ,isnull(ca.DataUsedMB,.1) AS DataUsedMB
        ,(CAST(pvt.[DATA FILE(s) SIZE (KB)] AS FLOAT)/1024) - isnull(ca.DataUsedMB,.1) AS DataFreeMB
        , CASE WHEN pvt.[DATA FILE(s) SIZE (KB)] = 0
			THEN 0
			ELSE ROUND((((CAST(pvt.[DATA FILE(s) SIZE (KB)] AS FLOAT)/1024) - isnull(ca.DataUsedMB,.1)) / (CAST(pvt.[DATA FILE(s) SIZE (KB)] AS FLOAT)/1024))*100, 2)
			END DataFreePercent
        ,CAST(pvt.[LOG FILE(s) SIZE (KB)] AS FLOAT)/1024 AS LogFileSizeMB
        ,CAST(pvt.[LOG FILE(s) Used SIZE (KB)] AS FLOAT)/1024 AS LogFileUsedMB
        ,(CAST(pvt.[LOG FILE(s) SIZE (KB)] AS FLOAT) - pvt.[LOG FILE(s) Used SIZE (KB)])/1024 LogFreeSizeMB
        ,pvt.[PERCENT LOG Used] AS LogUsedPercent
        ,100-pvt.[PERCENT LOG Used] AS LogFreePercent
    FROM sys.dm_os_performance_counters d
    PIVOT (SUM(cntr_value) FOR counter_name IN ([DATA FILE(s) SIZE (KB)],[LOG FILE(s) SIZE (KB)],[LOG FILE(s) Used SIZE (KB)],[PERCENT LOG Used])) pvt
    LEFT Outer Join (SELECT ips.database_id,SUM(page_count) AS page_count,CAST((SUM(page_count)*8) AS FLOAT)/1024 AS DataUsedMB
		FROM sys.dm_db_index_physical_stats(NULL,NULL,NULL,NULL,'sampled') ips
		GROUP BY ips.database_id
		) CA
		ON (ca.database_id = DB_ID(instance_name))
    WHERE OBJECT_NAME like '%databases%'
		and pvt.[DATA FILE(s) SIZE (KB)] IS not null
		And pvt.[LOG FILE(s) SIZE (KB)] IS not null
		And pvt.[LOG FILE(s) Used SIZE (KB)] IS not null
		And pvt.[PERCENT LOG Used] IS not null
		and instance_name <> '_Total'
    ORDER BY Database_ID
END

You will see that I did not entirely eliminate the looping mechanism. Truth be told, it is so much faster on servers with Large Databases.

Also take note of the DMV that is in use in this query. I am taking advantage of the performance monitor stats that are exposed through the DMV sys.dm_os_performance_counters. One caveat to this method, is that this DMV shows us the size of the resource database as well. I think it is fine to report that information back – but it won’t change much over time. It is for that purpose that I use the Left Join in the query.

The other trick that I utilized is to Pivot that performance monitor data. I think this works better than to write a bunch of sub-queries to generate the same sort of data layout.

You have probably also noted that I have chosen 250GB as the tipping point in this query. There is no particular reason for that size – just a large enough database size to make the point. For some, the tipping point may be a much smaller database size. Feel free to change this value to suit your needs.

Some other caveats. The perfmon cntr_value data is presented as an Int. For more accurate math operations, I chose to cast many of these to a Float.

I also ran into a case where the perfmon data was reporting 0 as the Data File Size for one of my databases. I had to throw a case statement in for the Data File Used Percent in order to avoid a Divide by 0 error.

I also wrapped the DataUsedMB in an ISNULL. This was due to the model and mssqlsystemresource databases not having data in the dm_db_physical_stats function. I could have left those as NULL, but wanted to show something for them both.

Check it out and Enjoy!

Table Space – CS Part Deux

by Jason Brimhall

Categories: News, Professional, Scripts, SSC

Tags: Documentation, SQL Script, Table Info, TSQL

Comments: No Comments

Published on: November 21, 2011

Another script, another day. And as promised, I am providing an update to the Table Space script that followed the sp_MStableSpace script. Not a lot more to be said about this one, since much was said already.

I updated the script for those CS collations. I also provided the update to show the % of DB column to be driven based on the data file usage information.

Code block

/* Part I */
--Drop Table #indstats
IF exists (SELECT * FROM tempdb.sys.objects WHERE name like '%#indstats%')
BEGIN
	DROP TABLE tempdb.dbo.#indstats
END
 
BEGIN
CREATE TABLE #indstats (
         IndStatsID INT PRIMARY KEY CLUSTERED
        ,database_id BIGINT
        ,index_id BIGINT
        ,IndexSizeMB DECIMAL(16,1)
        ,OBJECT_ID	BIGINT
);
END
 
INSERT INTO #indstats (IndStatsID,database_id,index_id,OBJECT_ID,IndexSizeMB)
        SELECT Row_Number() OVER (ORDER BY OBJECT_ID) AS IndStatsID
				,database_id,index_id,OBJECT_ID
				,CONVERT(DECIMAL(19,2),(SUM(ps.page_count))) * 8 /1024 AS IndexSizeMB
			FROM sys.dm_db_index_physical_stats(DB_ID(),null,NULL,NULL,'DETAILED') ps
			GROUP BY database_id,OBJECT_ID,index_id;
 
/* Part II */
DECLARE @dbsize DECIMAL(19,2)
 
SET NOCOUNT ON
 
/*
**  Summary data.
*/
BEGIN
	SELECT @dbsize = SUM(CONVERT(DECIMAL(19,2),CASE WHEN type = 0 THEN SIZE ELSE 0 END)) * 8/1024
		FROM sys.database_files
 
END
 
/* Part III */
BEGIN
	WITH RegData AS (
		SELECT a.container_id,p.OBJECT_ID,p.index_id,us.database_id
			,FileGroupName = FILEGROUP_NAME(a.data_space_id)
			,TableName = OBJECT_NAME(p.OBJECT_ID)
			,NumRows = p.ROWS
			,UsedPages = IsNull(a.used_pages,0)
			,TotalPages = IsNull(a.total_pages,0)
			,DataSizeMB = CONVERT(DECIMAL(19,2),IsNull(a.used_pages,0)) * 8/1024
			,IndexSizeMB = CASE WHEN ps.index_id < 2 THEN 0 ELSE ps.IndexSizeMB END
			,UserRequests = IsNull(us.user_seeks,0) + IsNull(us.user_scans,0) + IsNull(us.user_lookups,0)
			,UserUpdates = IsNull(us.user_updates,0)
			,LastUpdate = IsNull(us.last_user_update,null)
			,RatioRequestsToUpdates = CAST(IsNull(us.user_seeks,0)
					+ IsNull(us.user_scans,0) + IsNull(us.user_lookups,0) AS REAL)
				/ CAST(CASE us.user_updates WHEN 0 THEN 1 ELSE us.user_updates END AS REAL)
		FROM sys.allocation_units a
			INNER Join sys.partitions p
				ON p.hobt_id = a.container_id
				And a.type = 1
			LEFT Outer Join sys.dm_db_index_usage_stats us
				ON us.OBJECT_ID = p.OBJECT_ID
				And us.index_id = p.index_id
				And us.database_id = DB_ID()
			LEFT Outer Join #indstats ps
				ON p.index_id = ps.index_id
				And ps.database_id = DB_ID()
				And p.OBJECT_ID = ps.OBJECT_ID
		--WHERE OBJECTPROPERTY(p.object_id,'IsMSShipped') = 0
	)
	, LOBData AS (
		SELECT a.container_id,p.OBJECT_ID,p.index_id,us.database_id
			,FileGroupName = FILEGROUP_NAME(a.data_space_id)
			,TableName = OBJECT_NAME(p.OBJECT_ID)
			,NumRows = p.ROWS
			,UsedPages = IsNull(a.used_pages,0)
			,TotalPages = IsNull(a.total_pages,0)
			,DataSizeMB = CONVERT(DECIMAL(19,2),IsNull(a.used_pages,0)) * 8/1024
			,IndexSizeMB = CASE WHEN ps.index_id < 2 THEN 0 ELSE ps.IndexSizeMB END
			,UserRequests = IsNull(us.user_seeks,0) + IsNull(us.user_scans,0) + IsNull(us.user_lookups,0)
			,UserUpdates = IsNull(us.user_updates,0)
			,LastUpdate = IsNull(us.last_user_update,null)
			,RatioRequestsToUpdates = CAST(IsNull(us.user_seeks,0)
					+ IsNull(us.user_scans,0) + IsNull(us.user_lookups,0) AS REAL)
				/ CAST(CASE us.user_updates WHEN 0 THEN 1 ELSE us.user_updates END AS REAL)
		FROM sys.allocation_units a
			INNER Join sys.partitions p
				ON p.partition_id = a.container_id
				And a.type = 2
			LEFT Outer Join sys.dm_db_index_usage_stats us
				ON us.OBJECT_ID = p.OBJECT_ID
				And us.index_id = p.index_id
				And us.database_id = DB_ID()
			LEFT Outer Join #indstats ps
				ON p.index_id = ps.index_id
				And ps.database_id = DB_ID()
				And p.OBJECT_ID = ps.OBJECT_ID
		--WHERE OBJECTPROPERTY(p.object_id,'IsMSShipped') = 0
	)
	, OverFlowData AS (
		SELECT a.container_id,p.OBJECT_ID,p.index_id,us.database_id
			,FileGroupName = FILEGROUP_NAME(a.data_space_id)
			,TableName = OBJECT_NAME(p.OBJECT_ID)
			,NumRows = p.ROWS
			,UsedPages = IsNull(a.used_pages,0)
			,TotalPages = IsNull(a.total_pages,0)
			,DataSizeMB = CONVERT(DECIMAL(19,2),IsNull(a.used_pages,0)) * 8/1024
			,IndexSizeMB = CASE WHEN ps.index_id < 2 THEN 0 ELSE ps.IndexSizeMB END
			,UserRequests = IsNull(us.user_seeks,0) + IsNull(us.user_scans,0) + IsNull(us.user_lookups,0)
			,UserUpdates = IsNull(us.user_updates,0)
			,LastUpdate = IsNull(us.last_user_update,null)
			,RatioRequestsToUpdates = CAST(IsNull(us.user_seeks,0)
					+ IsNull(us.user_scans,0) + IsNull(us.user_lookups,0) AS REAL)
				/ CAST(CASE us.user_updates WHEN 0 THEN 1 ELSE us.user_updates END AS REAL)
		FROM sys.allocation_units a
			INNER Join sys.partitions p
				ON p.hobt_id = a.container_id
				And a.type = 3
			LEFT Outer Join sys.dm_db_index_usage_stats us
				ON us.OBJECT_ID = p.OBJECT_ID
				And us.index_id = p.index_id
				And us.database_id = DB_ID()
			LEFT Outer Join #indstats ps
				ON p.index_id = ps.index_id
				And ps.database_id = DB_ID()
				And p.OBJECT_ID = ps.OBJECT_ID
		--WHERE OBJECTPROPERTY(p.object_id,'IsMSShipped') = 0
	), IndexSum AS (
		SELECT a.OBJECT_ID
		,AllDataSizeMB = SUM(CASE WHEN a.index_id in (0,1)
							THEN IsNull(a.DataSizeMB,0) +  IsNull(p2.DataSizeMB,0) +  IsNull(p3.DataSizeMB,0)
							ELSE IsNull(p2.DataSizeMB,0) + IsNull(p3.DataSizeMB,0)
							END)
		FROM RegData a
			LEFT Outer Join LOBData p2
				ON p2.container_id = a.container_id
			LEFT Outer Join OverFlowData p3
				ON p3.container_id = a.container_id
		GROUP BY a.OBJECT_ID
	), SummaryInfo AS (
		SELECT
			TableName = MAX(a.TableName)
			,InRowDataSizeMB = SUM(IsNull(a.DataSizeMB,0))
			,LOBDataSizeMB = SUM(IsNull(p2.DataSizeMB,0))
			,OFlowDataSizeMB = SUM(IsNull(p3.DataSizeMB,0))
			,NumRows = MAX(COALESCE(a.NumRows,p2.NumRows,p3.NumRows,0))
			,AllUsedPages = SUM(IsNull(a.UsedPages,0) + IsNull(p2.UsedPages,0) + IsNull(p3.UsedPages,0))
			,AllPages = SUM(IsNull(a.TotalPages,0) + IsNull(p2.TotalPages,0) + IsNull(p3.TotalPages,0))
			,FreeDataSpace = CONVERT(DECIMAL(19,2),
				SUM(IsNull(a.TotalPages,0) + IsNull(p2.TotalPages,0) + IsNull(p3.TotalPages,0))
				- SUM(IsNull(a.UsedPages,0) + IsNull(p2.UsedPages,0) + IsNull(p3.UsedPages,0)))* 8 / 1024
			,AllDataSizeMB = MAX(ids.AllDataSizeMB)
			,IndexSizeMB = SUM(IsNull(a.IndexSizeMB,0))
				+ SUM(IsNull(p2.IndexSizeMB,0)) + SUM(IsNull(p3.IndexSizeMB,0))
			,UserRequests = AVG(IsNull(a.UserRequests,0)
				+ IsNull(p2.UserRequests,0) + IsNull(p3.UserRequests,0))
			,UserUpdates = AVG(IsNull(a.UserUpdates,0) + IsNull(p2.UserUpdates,0) + IsNull(p3.UserUpdates,0))
			,LastUpdate = MAX(COALESCE(a.LastUpdate,p2.LastUpdate,p3.LastUpdate,null))
			,DatabaseSize = @dbsize
		FROM RegData a
			LEFT Outer Join LOBData p2
				ON p2.container_id = a.container_id
			LEFT Outer Join OverFlowData p3
				ON p3.container_id = a.container_id
			LEFT Outer Join sys.indexes i
				ON i.OBJECT_ID = a.OBJECT_ID
				And i.index_id = a.index_id
			LEFT Outer Join IndexSum ids
				ON i.OBJECT_ID = ids.OBJECT_ID
		GROUP BY a.OBJECT_ID
	), TotalUnused AS (
				SELECT SUM(FreeDataSpace) AS UnusedSpace
					FROM SummaryInfo
		)
	SELECT TableName,NumRows,InRowDataSizeMB,LOBDataSizeMB,OFlowDataSizeMB
			,AllUsedPages,AllPages
			,FreeDataSpace,AllDataSizeMB,IndexSizeMB
			,TableSizeMB = AllDataSizeMB + IndexSizeMB + FreeDataSpace
			,UserRequests,UserUpdates,LastUpdate
			,PercentofDB = ((IndexSizeMB + AllDataSizeMB) / DatabaseSize) * 100
			,DatabaseSize
			,DataUsedSize = DatabaseSize - TU.UnusedSpace
			,PercentofDataFileUsed = ((IndexSizeMB + AllDataSizeMB) / (DatabaseSize - TU.UnusedSpace)) * 100
		FROM SummaryInfo SI
			CROSS APPLY TotalUnused TU
		ORDER BY PercentofDB DESC
END

Phew, I finally took care of some of those somedays that have been nagging me. Sure, there has been a someday that has evolved due to that – but that is a good thing.

It helps that I also need these scripts to be CS. Add to that, that I need to use them more frequently and it was a perfect opportunity to do a little housecleaning.

Table Space revised Again

by Jason Brimhall

Categories: News, Professional, Scripts, SSC

Tags: Documentation, SQL Script, TSQL

Comments: No Comments

Published on: November 17, 2011

Since I am in the Collation Sensitive mood, I am finally getting around to updating this script. This is the Table Space script that I have had out there for a while. In the last release of this script, a request (by Remi) was made to update it so it will work with CS. In addition to that, a request was made to add a few columns. I have done both.

The CS request was not too big of a deal – just took a minute to actually sit down and do it. Then it was a matter of setting a test database to CS and confirming that the script continued to work. A friend did the same legwork (thx Remi) and posted his update in a thread I had been planning on getting back to with the update. Now it will just get a link to this, and then there can be a circular reference.

The second part of the request was for a change in calculations and possibly additional columns. I just added columns and someday hope to get back to this script and parameterize the whole thing so that a variable set of columns can be returned – based on user input. Oh the glory of those someday goals.

So, here is the updated Table Size script.

Code block

DECLARE @dbsize DECIMAL(19,2)
        ,@logsize DECIMAL(19,2)
 
SET NOCOUNT ON
 
/*
**  Summary data.
*/
BEGIN
        SELECT @dbsize = SUM(CONVERT(DECIMAL(19,2),CASE WHEN type = 0 THEN SIZE ELSE 0 END)) * 8/1024
                , @logsize = SUM(CONVERT(DECIMAL(19,2),CASE WHEN type = 1 THEN SIZE ELSE 0 END)) * 8/1024
                FROM sys.database_files
 
END
        ;WITH FirstPass AS (
                SELECT OBJECT_ID,
                        ReservedPage = CONVERT(DECIMAL(19,2),SUM(reserved_page_count)) * 8/1024,
                        UsedPage = CONVERT(DECIMAL(19,2),SUM(used_page_count)) *8/1024,
                        PageCnt = SUM(
                        CONVERT(DECIMAL(19,2),CASE
                                WHEN (index_id < 2)
                                        THEN (used_page_count)
                                ELSE lob_used_page_count + row_overflow_used_page_count
                                END
                        )) * 8/1024,
                        RowCnt = SUM(
                        CASE
                                WHEN (index_id < 2)
                                        THEN row_count
                                ELSE 0
                        END
                        )
                FROM sys.dm_db_partition_stats
                --Where OBJECTPROPERTY(object_id,'IsMSShipped') = 0
                GROUP BY OBJECT_ID
        )
        ,InternalTables AS (
                SELECT ps.OBJECT_ID,
                        ReservedPage = CONVERT(DECIMAL(19,2),SUM(reserved_page_count)) * 8/1024,
                        UsedPage = CONVERT(DECIMAL(19,2),SUM(used_page_count)) *8/1024
                FROM sys.dm_db_partition_stats  ps
                        INNER Join sys.internal_tables it
                                ON it.OBJECT_ID = ps.OBJECT_ID
                                And it.internal_type IN (202,204,211,212,213,214,215,216)
                WHERE it.parent_id = ps.OBJECT_ID
					--And OBJECTPROPERTY(ps.object_id,'IsMSShipped') = 0
                GROUP BY ps.OBJECT_ID
        )
        ,Summary AS (
                SELECT
                        ObjName = OBJECT_NAME (F.OBJECT_ID),
                        NumRows = MAX(F.RowCnt),
                        ReservedPageMB = SUM(IsNull(F.ReservedPage,0) + IsNull(i.ReservedPage,0)),
                        DataSizeMB = SUM(F.PageCnt),
                        IndexSizeMB = SUM(CASE WHEN (F.UsedPage + IsNull(i.UsedPage,0)) > F.PageCnt
                                                        THEN ((F.UsedPage + IsNull(i.UsedPage,0)) - F.PageCnt) ELSE 0 END) ,-- Equivalent of max_record_size from sys.dm_db_index_physical_stats
                        UnusedSpace = SUM(CASE WHEN (F.ReservedPage + IsNull(i.ReservedPage,0)) > (F.UsedPage + IsNull(i.UsedPage,0))
                                THEN ((F.ReservedPage + IsNull(i.ReservedPage,0)) - (F.UsedPage + IsNull(i.UsedPage,0))) ELSE 0 END),
                        dbsizeMB = @dbsize,
                        LogSizeMB = @logsize
                FROM FirstPass F
                        LEFT Outer Join InternalTables i
                        ON i.OBJECT_ID = F.OBJECT_ID
                GROUP BY F.OBJECT_ID
        ),TotalUnused AS (
				SELECT SUM(UnusedSpace) AS UnusedSpace
					FROM Summary
		)
        SELECT ObjName,NumRows, ReservedPageMB, DataSizeMB, IndexSizeMB, S.UnusedSpace, dbsizeMB, LogSizeMB
						,dbsizeMB - TU.UnusedSpace AS TotalDataFreeSpace
                        ,PercentofDBPhysFile = ((IndexSizeMB + DataSizeMB) / @dbsize) * 100
                        ,PercentofDBUsedSpace = ((IndexSizeMB + DataSizeMB) / (@dbsize - TU.UnusedSpace)) * 100
 
        FROM Summary S
			CROSS APPLY TotalUnused TU
        ORDER BY PercentofDBUsedSpace DESC

If you recall, I did two versions of the table size script. One followed the path of sp_spaceused and the other followed sp_MStablespace. This script is the one that follows the sp_spaceused version. I will post an update for the sp_MStablespace version shortly.

Table Hierarchy goes CS

by Jason Brimhall

Categories: News, Professional, Scripts, SSC

Tags: Documentation, Foreign Key, Scripts, SQL Script, TSQL

Comments: No Comments

Published on: November 16, 2011

At the urging of a friend, this script is being updated for those that are dealing with Case Sensitivity. The first few rounds, I neglected Case Sensitivity and never tested for that. It makes sense to have this script updated for that if anybody out there is using it.

The updates are simple enough, it is just frustrating if you run into an error caused by CS and then you waste time troubleshooting it. Believe me, it has happened to me recently – and I don’t much like it.

Without further ado, here is the udpated script:

Code block

DECLARE    @StartParentTable    VARCHAR(256)
 
SELECT @StartParentTable = 'Calendar'
 
;With Hierarchy (ParentTable,ParentColumn, ChildTable,ChildColumn, FKLevel, SortCol) AS (
  SELECT OBJECT_NAME(sfk.referenced_object_id) AS ParentTable
          ,COL_NAME(sfkc.referenced_object_id,sfkc.referenced_column_id) AS ParentColumn
          ,OBJECT_NAME(sfk.Parent_object_id) AS ChildTable
          ,COL_NAME(sfkc.parent_object_id,sfkc.parent_column_id) AS ChildColumn
          ,0 AS FKLevel
          ,CAST('.'+CAST(OBJECT_NAME(sfk.referenced_object_id) AS VARCHAR(MAX))+'.' AS VARCHAR(MAX))
      FROM sys.foreign_key_columns sfkc
          INNER Join sys.foreign_keys sfk
              ON sfkc.constraint_object_id = sfk.OBJECT_ID
      WHERE OBJECT_NAME(sfk.referenced_object_id) = @StartParentTable
  UNION All
  SELECT OBJECT_NAME(sfk.referenced_object_id) AS ParentTable
          ,COL_NAME(sfkc.referenced_object_id,sfkc.referenced_column_id) AS ParentColumn
          ,OBJECT_NAME(sfk.Parent_object_id) AS ChildTable
          ,COL_NAME(sfkc.parent_object_id,sfkc.parent_column_id) AS ChildColumn
          ,FKLevel + 1
          ,STUFF(('.' + F.SortCol + CAST(CAST(OBJECT_NAME(sfk.referenced_object_id) AS VARCHAR(MAX)) + '.' AS VARCHAR(MAX))
                      ),1,1,'')
      FROM sys.foreign_keys sfk
          INNER Join Hierarchy F
              ON OBJECT_NAME(sfk.referenced_object_id) = F.ChildTable
              And F.SortCol NOT LIKE '%'+CAST(OBJECT_NAME(sfk.referenced_object_id) AS VARCHAR(MAX))+'%'
          INNER Join sys.foreign_key_columns sfkc
              ON sfkc.constraint_object_id = sfk.OBJECT_ID
      WHERE OBJECT_NAME(sfk.referenced_object_id) <> @StartParentTable
          And sfk.referenced_object_id <> sfk.parent_object_id
), Ancestry (ParentTable,ParentColumn, ChildTable,ChildColumn, FKLevel, SortCol) AS (
  SELECT DISTINCT OBJECT_NAME(sfk.referenced_object_id) AS ParentTable
      ,COL_NAME(sfkc.referenced_object_id,sfkc.referenced_column_id) AS ParentColumn
      ,OBJECT_NAME(sfk.Parent_object_id) AS ChildTable
      ,COL_NAME(sfkc.parent_object_id,sfkc.parent_column_id) AS ChildColumn
      ,-1 AS FKLevel
      ,CAST('.'+CAST(OBJECT_NAME(sfk.referenced_object_id) AS VARCHAR(MAX))+'.' AS VARCHAR(MAX))
  FROM Hierarchy F
      INNER Join sys.foreign_keys sfk
          ON F.ChildTable = OBJECT_NAME(sfk.parent_object_id)
          And F.ParentTable <> OBJECT_NAME(sfk.referenced_object_id)
      INNER Join sys.foreign_key_columns sfkc
          ON sfkc.constraint_object_id = sfk.OBJECT_ID
  UNION All
  SELECT OBJECT_NAME(sfk.referenced_object_id) AS ParentTable
      ,COL_NAME(sfkc.referenced_object_id,sfkc.referenced_column_id) AS ParentColumn
      ,OBJECT_NAME(sfk.Parent_object_id) AS ChildTable
      ,COL_NAME(sfkc.parent_object_id,sfkc.parent_column_id) AS ChildColumn
      ,F.FKLevel -1
      ,STUFF(('.' + SortCol + CAST(CAST(OBJECT_NAME(sfk.referenced_object_id) AS VARCHAR(MAX)) + '.' AS VARCHAR(MAX))
                  ),1,1,'')
  FROM Ancestry F
      INNER Join sys.foreign_keys sfk
          ON F.ParentTable = OBJECT_NAME(sfk.parent_object_id)
      INNER Join sys.foreign_key_columns sfkc
          ON sfkc.constraint_object_id = sfk.OBJECT_ID
  WHERE F.ParentTable not in (SELECT ParentTable FROM Hierarchy)
      And sfk.referenced_object_id <> sfk.parent_object_id
      And F.ChildTable not IN (SELECT ChildTable FROM Hierarchy)
)
 
SELECT ParentTable,ParentColumn, ChildTable,ChildColumn, FKLevel, SortCol
FROM Hierarchy
UNION All
SELECT ParentTable,ParentColumn, ChildTable,ChildColumn, FKLevel, SortCol
FROM Ancestry
  ORDER BY SortCol ASC
  OPTION (maxrecursion 500)

Still on the todo list is to make this bad boy run faster in the event of circular references. If you find something else with it that you think could use adjusting, let me know.

TSQL Challenge 63 – Update

by Jason Brimhall

Categories: News, Professional, Scripts, SSC

Tags: Scripts, Sudoku, TSQL Challenge

Comments: No Comments

Published on: November 14, 2011

If you recall, I like Sudoku. I even posted a script for solving it via TSQL. I went so far as to enter my script into a TSQL Challenge. That all started way back in August. Today, I have an update!!

I was notified this morning from BeyondRelational.com that I have earned a new badge. Cool, what’s the badge? I clicked the link and it took me to this badge.
Huh? I’m a winner of the SQL Sudoku Challenge? Awesome!

Looking it over, I am winner #3. This means I could have done better with my solution. And looking at the other solution stats, it appears I will need to find time to see what the others did to make their solutions go sooooo fast. I have some learning to do – woohoo.

So, now that means I need to post my solution.

Code block

--
--These Variables are intended to be used as input parameters if made into a proc.
DECLARE @SudokuNo INT = 3 --my script is setup to allow the table to contain multiple puzzles.
		,@SudokuGivens VARCHAR(100) = '53  7    6  195    98    6 8   6   34  8 3  17   2   6 6    28    419  5    8  79'
									--'  15       6     7    9  4   5   1  9   4   8  3   6   2  7    8     7       35  '
									--'   9 1   6     5            7 2   1        29    3    4   6 7 55     8     1     '
		,@FromTableorString	TINYINT = 1 --1 = run from TC63, else run from Input Parm
 
DECLARE @SudTable	TABLE (RowCol INT PRIMARY KEY CLUSTERED, ConcatRow VARCHAR(10))
 
IF @FromTableorString = 1
BEGIN
--Populate Data for missing vectors (Col/Row) with A space
--Use an Isnull and Outer Apply in case there are no givens for a particular row.
	WITH dual(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT N)) FROM ( VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9)) x(N)
	),rowcols AS (SELECT DISTINCT ROW.N AS ROW,Col.N AS Col FROM Dual ROW Cross Apply Dual Col)
 
	--Concatenate into a string if the SudokuSource is a Table
	SELECT @SudokuGivens = (SELECT  Isnull(t.DATA,0)
		FROM TC63 T
		RIGHT Outer Join rowcols D
			ON D.ROW = T.ROW
			And D.Col = T.Col
			And SudokuNo = @SudokuNo
		FOR xml PATH(''))  
 
END
	SELECT @SudokuGivens = REPLACE(@SudokuGivens,'0',' ') --If from table, replace commas.  From a String can have spaces or commas
 
--Solve the Sudoku - into a string
;WITH x( s, ind ) AS
( SELECT @SudokuGivens, CHARINDEX(' ',@SudokuGivens ) AS ind
  UNION all
  SELECT CONVERT(VARCHAR(100),SUBSTRING( s, 1, ind - 1 ) + CONVERT(CHAR(1),z) + SUBSTRING( s, ind + 1 ,81))
       , CHARINDEX(' ', s, ind + 1 ) AS ind
  FROM x
    CROSS APPLY ( VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9)) z (z)
  WHERE ind > 0
  and not exists (SELECT null
					FROM ( VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9)) ww (lp)
					WHERE z = SUBSTRING( s, ( ind - 1)% 9  - 8 + lp * 9, 1 )
						or    z = SUBSTRING( s, ( ( ind - 1 ) / 9 ) * 9 + lp, 1 )
						or    z = SUBSTRING( s, (( ( ind - 1 ) / 3 )%3) * 3
                                      + ( ( ind - 1 ) / 27 ) * 27 + lp
                                      + ( ( lp - 1 ) / 3 ) * 6
                                   , 1 )
                 )
), Sud AS (
--Create a 9 record result set that has the string solution duplicated 9 times.  Then show only relevant 9 data for each row
SELECT TOP 9 SUBSTRING(s, ROW_NUMBER() OVER (ORDER BY s) * 9 - 8, ROW_NUMBER() OVER (ORDER BY s) * 9 - (ROW_NUMBER() OVER (ORDER BY s) * 9 - 9)) AS ConcatRow
FROM x
Cross Apply ( VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9)) y(N)
WHERE ind = 0
)
 
--Populate a Table Variable for further Row/Col manipulation
INSERT INTO @SudTable (RowCol,ConcatRow)
	SELECT Row_Number() OVER (ORDER BY (SELECT 1)),ConcatRow
		FROM Sud
 
--Pivot the data out to produce a 9x9 grid
SELECT @SudokuNo AS SudokuNo,c1.RowCol,[1],[2],[3],[4],[5],[6],[7],[8],[9]
	FROM @SudTable S
	Cross Apply (SELECT RowCol,[1],[2],[3],[4],[5],[6],[7],[8],[9]
					FROM (SELECT S.RowCol
							,ColNo = Row_Number() OVER (Partition BY RowCol ORDER BY ConcatRow)
							,DATA = SUBSTRING(ConcatRow, ROW_NUMBER() OVER (Partition BY S.RowCol ORDER BY ConcatRow), 1)
					FROM @SudTable S
						Cross Apply ( VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9)) x(N)
						) Intr
						Pivot (
						MAX(DATA)
						FOR ColNo IN ([1],[2],[3],[4],[5],[6],[7],[8],[9])
		) PVT) AS C1
	WHERE c1.RowCol = S.RowCol
	ORDER BY S.RowCol ASC

Sadly, that is not the most recent version of the script that I had. I had intended on submitting this version, which is still slightly faster.

Code block

--
--These Variables are intended to be used as input parameters if made into a proc.
DECLARE @SudokuNo INT = 3 --my script is setup to allow the table to contain multiple puzzles.
		,@SudokuGivens VARCHAR(100) = '53  7    6  195    98    6 8   6   34  8 3  17   2   6 6    28    419  5    8  79'
									--'  15       6     7    9  4   5   1  9   4   8  3   6   2  7    8     7       35  '
									--'   9 1   6     5            7 2   1        29    3    4   6 7 55     8     1     '
		,@FromTableorString	TINYINT = 1 --1 = run from TC63, else run from Input Parm
 
DECLARE @SudTable	TABLE (RowCol INT PRIMARY KEY CLUSTERED, ConcatRow VARCHAR(10))
 
IF @FromTableorString = 1
BEGIN
--Populate Data for missing vectors (Col/Row) with A space
--Use an Isnull and Outer Apply in case there are no givens for a particular row.
	WITH dual(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT N)) FROM ( VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9)) x(N)
	),rowcols AS (SELECT DISTINCT ROW.N AS ROW,Col.N AS Col FROM Dual ROW Cross Apply Dual Col)
 
	--Concatenate into a string if the SudokuSource is a Table
	SELECT @SudokuGivens = (SELECT  Isnull(t.DATA,0)
		FROM TC63 T
		RIGHT Outer Join rowcols D
			ON D.ROW = T.ROW
			And D.Col = T.Col
			And SudokuNo = @SudokuNo
		FOR xml PATH(''))  
 
END
	SELECT @SudokuGivens = REPLACE(@SudokuGivens,'0',' ') --If from table, replace commas.  From a String can have spaces or commas
 
--Solve the Sudoku - into a string
;WITH x( s, ind ) AS
( SELECT @SudokuGivens, CHARINDEX(' ',@SudokuGivens ) AS ind
  UNION all
  SELECT CONVERT(VARCHAR(100),SUBSTRING( s, 1, ind - 1 ) + CONVERT(CHAR(1),z) + SUBSTRING( s, ind + 1 ,81))
       , CHARINDEX(' ', s, ind + 1 ) AS ind
  FROM x
    CROSS APPLY ( VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9)) z (z)
  WHERE ind > 0
  and not exists (SELECT null
					FROM ( VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9)) ww (lp)
					WHERE z = SUBSTRING( s, ( ind - 1)% 9  - 8 + lp * 9, 1 )
						or    z = SUBSTRING( s, ( ( ind - 1 ) / 9 ) * 9 + lp, 1 )
						or    z = SUBSTRING( s, (( ( ind - 1 ) / 3 )%3) * 3
                                      + ( ( ind - 1 ) / 27 ) * 27 + lp
                                      + ( ( lp - 1 ) / 3 ) * 6
                                   , 1 )
                 )
), Sud AS (
--Create a 9 record result set that has the string solution duplicated 9 times.  Then show only relevant 9 data for each row
SELECT TOP 9 SUBSTRING(s, ROW_NUMBER() OVER (ORDER BY s) * 9 - 8, ROW_NUMBER() OVER (ORDER BY s) * 9 - (ROW_NUMBER() OVER (ORDER BY s) * 9 - 9)) AS ConcatRow
FROM x
Cross Apply ( VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9)) y(N)
WHERE ind = 0
)
 
--Populate a Table Variable for further Row/Col manipulation
INSERT INTO @SudTable (RowCol,ConcatRow)
	SELECT Row_Number() OVER (ORDER BY (SELECT 1)),ConcatRow
		FROM Sud
 
--Pivot the data out to produce a 9x9 grid
SELECT @SudokuNo AS SudokuNo,c1.RowCol,[1],[2],[3],[4],[5],[6],[7],[8],[9]
	FROM @SudTable S
	Cross Apply (SELECT RowCol,[1],[2],[3],[4],[5],[6],[7],[8],[9]
					FROM (SELECT S.RowCol
							,ColNo = Row_Number() OVER (Partition BY RowCol ORDER BY ConcatRow)
							,DATA = SUBSTRING(ConcatRow, ROW_NUMBER() OVER (Partition BY S.RowCol ORDER BY ConcatRow), 1)
					FROM @SudTable S
						Cross Apply ( VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9)) x(N)
						) Intr
						Pivot (
						MAX(DATA)
						FOR ColNo IN ([1],[2],[3],[4],[5],[6],[7],[8],[9])
		) PVT) AS C1
	WHERE c1.RowCol = S.RowCol
	ORDER BY S.RowCol ASC

Still, I am certain that (without having looked at the other winning solutions) this is not on par with the best solutions. And I have a lot to learn.

Quick and Dirty Index Info

by Jason Brimhall

Categories: News, Professional, Scripts, SSC

Tags: Index, SQL Script

Comments: No Comments

Published on: November 1, 2011

From time to time, somebody needs to find some information about the indexes in the database. As DBA’s, there are a few tools at your disposal to look up that information. The required/requested information is not always the same. That said, you can usually adapt a query quickly to find what you need.

Where to Look

SQL 2005 and up have provided us with some very useful means to gain insight into the indexes in our databases. The following DMOs is a small representation of that information.

sys.dm_db_missing_index_details
sys.dm_db_missing_index_group_stats
sys.dm_db_missing_index_groups
sys.dm_db_index_usage_stats
sys.dm_db_index_physical_stats

Add to that, there are some catalog views to help with finding information relevant to indexes in the databases.

sys.indexes
sys.index_columns

With just these few objects, there is a wealth of information at your fingertips. I often find myself querying for information about indexes. I also find myself being asked how to find this information on a relatively frequent interval. Usually, I just end up writing out the query again (depends on mood and day).

Rewriting the query doesn’t take too much time when you know what you need to query. That said, sometimes it is nice to have a base query ready to go. From this query you can add/remove items as you see fit to get the information that you really need.

For instance, here is a query to get some of the fragmentation information. I would probably just re-use this query over and over where applicable – only changing the parts that mattered to get the necessary result set.

Code block

SELECT OBJECT_NAME(ps.OBJECT_ID),I.name,index_level,index_type_desc
		,I.is_hypothetical,ps.page_count,ps.record_count,ps.avg_fragmentation_in_percent
		,ps.ghost_record_count
		,ps.compressed_page_count
	FROM sys.dm_db_index_physical_stats(DB_ID(),null,null,null,'detailed') ps
		INNER Join sys.indexes I
			ON ps.OBJECT_ID = I.OBJECT_ID
			And ps.index_id = I.index_id
	ORDER BY OBJECT_NAME(ps.OBJECT_ID),ps.index_id,ps.index_level

There are many types of queries we could write for the different relevant information pertaining to our indexes. It is all a matter of knowing where to start. I hope that knowing about these DMOs and catalog views, it will help shorten your efforts to retrieving this information.

Column Level Permissions

by Jason Brimhall

Categories: News, Professional, Scripts, SSC

Tags: Audit, Scripts, Security

Comments: No Comments

Published on: September 19, 2011

Did you know that you can grant permissions down to the column level in SQL Server? Well, if you didn’t know that – you do now.

It is actually rather simple to grant permissions at the column level. This can be demonstrated by the following script.

Code block

GRANT SELECT ON ColorPlate (ColorID) TO testU

If you want to check out more on that syntax, read here.

And then…

Why is it important to know that you can do this? Well, it is quite possible you have some of these permissions already in place. It is possible you may have inherited something like this. Just maybe there is a business requirement requiring that certain users or groups only have access to certain data within certain columns.

That brings up a new problem then. How do you find out what columns have specific permissions applied to certain users? Well, that is actually pretty straight forward. We can query the system views and determine column level permissions.

Code block

SELECT dp.grantee_principal_id,p.name AS UName
		,dp.permission_name,c.name
		,OBJECT_NAME(o.OBJECT_ID) AS TabName
	FROM sys.database_permissions dp
	INNER JOIN Sys.objects O
		ON dp.major_id = o.OBJECT_ID
	INNER JOIN sys.columns C
		ON c.OBJECT_ID = O.OBJECT_ID
		AND c.column_id = dp.minor_id
	INNER JOIN sys.database_principals P
		ON p.principal_id = dp.grantee_principal_id

The previous query is a really simple version of how to find this information. As you can see, I am simply returning the UserName, TableName and ColumnName along with the permission in effect on that column.

You should also be able to see that the mapping between these system views is pretty straight forward as well. Major_id maps to object_id and column_id maps to minor_id.

Conclusion

This query can be of good use to determine permissions in place for columns in tables within your database. Furthermore, you can even use this query to simply test your curiosity as you check to determine what has been put into effect in the databases you manage.

There are more complex methods to determine these permissions. With there being more complex methods, I am sure there are also some easier methods. Let me know what you do to query these permissions.

Activity Monitor and Profiler

by Jason Brimhall

Categories: News, Professional, Scripts, SQLServerPedia Syndication, SSC

Tags: Scripts, TSQL

Comments: 2 Comments

Published on: July 28, 2011

Today I came across a link to a neat little script for SQL 2005 / 2008 to help derive the head blocker in a blocking chain. That script can be found here. I was looking at the script and thought it looked pretty cool and also wondered why it might look a little familiar. Not too big of a concern since many admin type scripts can look familiar.

Then I noticed that the title contained the words Activity Monitor. I wondered to myself, was this taken from activity monitor? So, I decided to play with Profiler for a bit to see if I could generate the same query. The process really isn’t too difficult. Here is what I did.

Open Activity Monitor. Simple enough to do. Right click the instance (in SSMS) you wish to monitor, select Activity Monitor from the context menu.
Open Profiler. Again from within SSMS, click the tools menu and then select SQL Server Profiler.
Start a New trace. When starting the trace ensure that you are connecting to the same instance as you started Activity Monitor. Select the TSQL-Replay template (other tsql will probably work, this is the one that I used). Goto the events selection tab and click on the Column Filters button. Select “LoginName” and then expand “Like” on the right hand side. In the new text box, type the login you are using to authenticate to the SQL Instance in question (domain\user or SQLLogin).
Click run and browse through the results for a bit. It shouldn’t take too long for that query to pop up.
Once you find the query, make sure you stop the trace.

What is the point in this? Well, as explained in that other blog, sometimes it is just better to run a query than to run Activity Monitor. Furthermore, you can take this method and trap many of the queries that Microsoft built into that tool. You can either use them for learning or troubleshooting. This is just one more tool to have in your arsenal in order to become better at your job.

Foreign Key Hierarchy Update

by Jason Brimhall

Categories: News, Professional, Scripts, SSC

Tags: Documentation, Foreign Key, Hierarchy, Primary Key, Scripts

Comments: 1 Comment

Published on: July 11, 2011

Today I would like to revisit a post of mine that is rather old. More precisely, the script in that post needs revisiting. This is one of my more favorite scripts and I still have more ideas to implement with it. The post/script in question can be found here.

In revisiting this script, I simplified it a bit. I also had to work on a problem with it that occurs in Hierarchies with circular dependencies. Quite frankly, that was a huge pain in the butt. There are some tricks out there to try and help with it – but I was having a hard time getting any of them to work in this scenario. I also updated the script to better handle self-referencing objects.

When you have circular references, and are trying to recurse the tree via a CTE, an instant blocker comes into play. You can only reference the anchor of the Recursive CTE once. Fixing a circular reference would be many times easier if you could reference the anchor twice.

In the end, the biggest hint to getting this to work came from this post. For it to work, I needed to find which combination of fields would work best. I finally settled on using the Object_ID to help reduce my pain. I settled on using the following in the anchor:

Code block

CAST('.'+CAST(OBJECT_NAME(sfk.referenced_object_id) AS VARCHAR(MAX))+'.' AS VARCHAR(MAX))

and the following in the recursive definition.

Code block

STUFF(('.' + F.SortCol + CAST(CAST(OBJECT_NAME(sfk.referenced_object_id) AS VARCHAR(MAX)) + '.' AS VARCHAR(MAX))
						),1,1,'')

You can see that I am concatenating into a string for this column. This seems to work well for the purpose of eliminating those circular references.

Other adjustments to the script are not quite as significant but there is a small performance gain to be seen by these subtle changes. The most notable is the change to remove the two joins out to sys.columns in order to get the column names of the Parent and Child objects. In lieu of these joins, I am using the COL_NAME() function. This little change came to me thanks to a little work done last week on my statistics script that you can read here.

The final notable change comes in the naming of the CTEs in this script. I decided to rename the CTEs to something a bit more meaningful. In this case, Hierarchy and Ancestry are much more appropriate.

Without further adieu, here is the next major revision of that script.

Code block

DECLARE	@StartParentTable	VARCHAR(256)
 
SELECT @StartParentTable = 'yourtable'
 
;With Hierarchy (ParentTable,ParentColumn, ChildTable,ChildColumn, FKLevel, SortCol) AS (
	SELECT OBJECT_NAME(sfk.referenced_object_id) AS ParentTable
			,COL_NAME(sfkc.referenced_object_id,sfkc.referenced_column_id) AS ParentColumn
			,OBJECT_NAME(sfk.Parent_object_id) AS ChildTable
			,COL_NAME(sfkc.parent_object_id,sfkc.parent_column_id) AS ChildColumn
			,0 AS FKLevel
			,CAST('.'+CAST(OBJECT_NAME(sfk.referenced_object_id) AS VARCHAR(MAX))+'.' AS VARCHAR(MAX))
		FROM sys.foreign_key_columns sfkc
			INNER Join sys.foreign_keys sfk
				ON sfkc.constraint_object_id = sfk.OBJECT_ID
		WHERE OBJECT_NAME(sfk.referenced_object_id) = @StartParentTable
	UNION All
	SELECT OBJECT_NAME(sfk.referenced_object_id) AS ParentTable
			,COL_NAME(sfkc.referenced_object_id,sfkc.referenced_column_id) AS ParentColumn
			,OBJECT_NAME(sfk.Parent_object_id) AS ChildTable
			,COL_NAME(sfkc.parent_object_id,sfkc.parent_column_id) AS ChildColumn
			,FKLevel + 1
			,STUFF(('.' + F.SortCol + CAST(CAST(OBJECT_NAME(sfk.referenced_object_id) AS VARCHAR(MAX)) + '.' AS VARCHAR(MAX))
						),1,1,'')
		FROM sys.foreign_keys sfk
			INNER Join Hierarchy F
				ON OBJECT_NAME(sfk.referenced_object_id) = f.ChildTable
				And F.SortCol NOT LIKE '%'+CAST(OBJECT_NAME(sfk.referenced_object_id) AS VARCHAR(MAX))+'%'
			INNER Join sys.foreign_key_columns sfkc
				ON sfkc.constraint_object_id = sfk.OBJECT_ID
		WHERE OBJECT_NAME(sfk.referenced_object_id) <> @StartParentTable
			And sfk.referenced_object_id <> sfk.parent_object_id
), Ancestry (ParentTable,ParentColumn, ChildTable,ChildColumn, FKLevel, SortCol) AS (
	SELECT DISTINCT OBJECT_NAME(sfk.referenced_object_id) AS ParentTable
		,COL_NAME(sfkc.referenced_object_id,sfkc.referenced_column_id) AS ParentColumn
		,OBJECT_NAME(sfk.Parent_object_id) AS ChildTable
		,COL_NAME(sfkc.parent_object_id,sfkc.parent_column_id) AS ChildColumn
		,-1 AS FKLevel
		,CAST('.'+CAST(OBJECT_NAME(sfk.referenced_object_id) AS VARCHAR(MAX))+'.' AS VARCHAR(MAX))
	FROM Hierarchy F
		INNER Join sys.foreign_keys sfk
			ON f.ChildTable = OBJECT_NAME(sfk.parent_object_id)
			And F.ParentTable <> OBJECT_NAME(sfk.referenced_object_id)
		INNER Join sys.foreign_key_columns sfkc
			ON sfkc.constraint_object_id = sfk.OBJECT_ID
	UNION All
	SELECT OBJECT_NAME(sfk.referenced_object_id) AS ParentTable
		,COL_NAME(sfkc.referenced_object_id,sfkc.referenced_column_id) AS ParentColumn
		,OBJECT_NAME(sfk.Parent_object_id) AS ChildTable
		,COL_NAME(sfkc.parent_object_id,sfkc.parent_column_id) AS ChildColumn
		,f.FKLevel -1
		,STUFF(('.' + SortCol + CAST(CAST(OBJECT_NAME(sfk.referenced_object_id) AS VARCHAR(MAX)) + '.' AS VARCHAR(MAX))
					),1,1,'')
	FROM Ancestry F
		INNER Join sys.foreign_keys sfk
			ON f.parentTable = OBJECT_NAME(sfk.parent_object_id)
		INNER Join sys.foreign_key_columns sfkc
			ON sfkc.constraint_object_id = sfk.OBJECT_ID
	WHERE f.parentTable not in (SELECT ParentTable FROM Hierarchy)
		And sfk.referenced_object_id <> sfk.parent_object_id
		And f.ChildTable not IN (SELECT ChildTable FROM Hierarchy)
)
 
SELECT ParentTable,ParentColumn, ChildTable,ChildColumn, FKLevel, SortCol
FROM Hierarchy
UNION All
SELECT ParentTable,ParentColumn, ChildTable,ChildColumn, FKLevel, SortCol
FROM Ancestry
	ORDER BY SortCol ASC
	OPTION (maxrecursion 500)

I hope you will play with this script, test it out and make recommendations or even find bugs with it and let me know.

page 1 of 3»

February 2012
M	T	W	T	F	S	S
« Jan
	1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29

Content

Now Reading

Planned books:

Throne of Fire, the by Rick Riordan
Seeds of Rebellion (Beyonders) by Brandon Mull

Current books:

ChiRunning: A Revolutionary Approach to Effortless, Injury-Free Running by Danny Dreyer, Katherine Dreyer
Advanced Marathoning – 2nd Edition by Peter Pfitzinger, Scott Douglas
SQL Server MVP Deep Dives by Nielsen Paul, Delaney Kalen, Machanic Adam, Tripp Kimberly, Randal Paul, Low Greg
A World Without Heroes (Beyonders) by Brandon Mull

Recent books:

The Red Pyramid (The Kane Chronicles, Book 1) by Rick Riordan
Fablehaven, Book 5:Keys to the Demon Prison by Brandon Mull
Catching Fire (The Second Book of the Hunger Games) by Suzanne Collins
Pirate Latitudes by Michael Crichton
The Battle of the Labyrinth (Percy Jackson and the Olympians, Book 4) by Rick Riordan

View full Library

SQL RNNR

A Trio of Functions

Database Data and Log Size Info

Table Space – CS Part Deux

Table Space revised Again

Table Hierarchy goes CS

TSQL Challenge 63 – Update

Quick and Dirty Index Info

Where to Look

Column Level Permissions

And then…

Conclusion

Activity Monitor and Profiler

Foreign Key Hierarchy Update

Categories

Popular Posts

Blogroll

Now Reading

Planned books:

Current books:

Recent books:

SQLHelp