Table Space updated again

Categories: News, Professional, Scripts, SSC
Comments: No Comments
Published on: March 28, 2014

Today we have another update for an age old script on this site.  You can find the last update to the script here.

This time we have a bit of a bug update.  The details of that bug are in the notes for the script.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
 
/*
Purpose:
To Evaluate table sizes combined with index space consumption to determine higher cost tables in 
terms of storage, resources and maintenance needs.
 
ModifiedDate	ModifiedBy	Description
2013-11-21		JB			Tables without Indexes had a Null Value in the Output
							Fixed the output to sum properly for the NULLS in absence of an index
 
*/
BEGIN TRAN
 
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)) / 128.0
                , @logsize = SUM(CONVERT(DECIMAL(19,2),CASE WHEN type = 1 THEN SIZE ELSE 0 END)) / 128.0
                FROM sys.database_files
 
END;
        WITH FirstPass AS (
                SELECT OBJECT_ID,
                        ReservedPage = CONVERT(DECIMAL(19,2),SUM(reserved_page_count)) / 128.0,
                        UsedPage = CONVERT(DECIMAL(19,2),SUM(used_page_count)) / 128.0,
                        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,
                        iPageCnt = SUM(
                        CONVERT(DECIMAL(19,2),CASE
                                WHEN (index_id > 1)
                                        THEN (used_page_count)
                                ELSE 0
                                END
                        )) * 8/1024,
                        RowCnt = SUM(
                        CASE
                                WHEN (index_id < 2)
                                        THEN row_count
                                ELSE 0
                        END
                        )
                FROM sys.dm_db_partition_stats
                WHERE 1 = 1
					--AND OBJECTPROPERTY(object_id,'IsMSShipped') = 0
					AND index_id < 2
                GROUP BY OBJECT_ID
        ),IndexPass AS (
                SELECT OBJECT_ID,
                        iReservedPage = CONVERT(DECIMAL(19,2),SUM(reserved_page_count)) / 128.0
                        ,iUsedPage = CONVERT(DECIMAL(19,2),SUM(used_page_count)) / 128.0
                        ,iPageCnt = SUM(
                        CONVERT(DECIMAL(19,2),CASE
                                WHEN (index_id > 1)
                                        THEN (used_page_count)
                                ELSE 0
                                END
                        )) / 128.0
                        ,RowCnt = SUM(
                        CASE
                                WHEN (index_id < 2)
                                        THEN row_count
                                ELSE 0
                        END
                        )
                FROM sys.dm_db_partition_stats
                WHERE 1 = 1 
					--AND OBJECTPROPERTY(object_id,'IsMSShipped') = 0
					AND index_id > 1
                GROUP BY OBJECT_ID
        ),InternalTables AS (
                SELECT ps.OBJECT_ID,
                        ReservedPage = CONVERT(DECIMAL(19,2),SUM(reserved_page_count)) / 128.0
                        ,UsedPage = CONVERT(DECIMAL(19,2),SUM(used_page_count)) / 128.0
                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
                        OBJECT_NAME (F.OBJECT_ID) AS ObjName 
                        ,SCHEMA_NAME(o.schema_id) AS SchemaName
                        ,CASE WHEN OBJECTPROPERTY(F.OBJECT_ID,'IsMSShipped') = 1 
							THEN 'YES' 
							ELSE 'NO' 
							END AS IsMsShipped
                        ,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
						,IndexSizeMB = SUM(ISNULL(ip.iPageCnt,0))
                        ,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)
                                + (SUM(ISNULL(ip.iReservedPage,0)) - SUM(ISNULL(ip.iUsedPage,0)))
                        ,IndexReservedMB = SUM(ISNULL(ip.iReservedPage,0))
                        ,dbsizeMB = @dbsize
                        ,LogSizeMB = @logsize
                FROM FirstPass F
					INNER JOIN sys.objects o
						ON F.OBJECT_ID = o.OBJECT_ID
					LEFT Outer Join InternalTables i
                        ON i.OBJECT_ID = F.OBJECT_ID
                    LEFT OUTER JOIN IndexPass ip
						ON F.OBJECT_ID = ip.OBJECT_ID
                GROUP BY F.OBJECT_ID,o.schema_id
        ),TotalUnused AS (
				SELECT SUM(ISNULL(UnusedSpace,0)) AS UnusedSpace
						,SUM(ISNULL(reservedpageMB,0))+SUM(ISNULL(IndexReservedMB,0)) AS Usedr
					FROM Summary
		)
        SELECT ObjName,SchemaName,IsMsShipped,NumRows, ReservedPageMB, ISNULL(DataSizeMB,0) AS DataSizeMB, ISNULL(IndexSizeMB,0) AS IndexSizeMB
						,ISNULL(S.UnusedSpace,0) AS UnusedSpace, dbsizeMB, LogSizeMB
						,TU.UnusedSpace AS TotalTableFreeSpace 
                        ,dbsizeMB - TU.Usedr AS DataFileFreeSpace  
                        /*within 1.5gb on a 1.76tb database or .000085% variance or 99.999915% accuracy */
                        ,PercentofDBPhysFile = ((ISNULL(IndexSizeMB,0) + ISNULL(DataSizeMB,0)) / @dbsize) * 100
                        ,PercentofDBUsedSpace = ((ISNULL(IndexSizeMB,0) + ISNULL(DataSizeMB,0)) / (@dbsize - TU.UnusedSpace)) * 100
 
        FROM Summary S
			CROSS APPLY TotalUnused TU
        ORDER BY PercentofDBUsedSpace DESC
 
ROLLBACK

Table Hierarchy updated

Recently a need resurfaced to explore the foreign key tree (hierarchy/genealogy) as it related to a specific table within a database.  As I pulled out the script from the repository, I realized there was some unfinished work to be done.  But there was also some polish that needed to be added.  This is an update to the most recent posting of that script.  You can see several revisions in the series at this link or the group here.

Some of the changes involve formatting and and labeling.  I added a new column called “Direction” to help understand the relationship of the key to the table in question.  I also changed up the FKGenealogy (formerly called SortCol) to reflect the source table more accurately in the case when the key comes from an ancestor rather than a descendant.  The Level of the FK was also modified to help understand a little better how far away the ancestor was in relationship to the origin table.

A final adjustment also comes from the Genealogy attribute.  Ancestors were all starting at the wrong point in the lineage.  I adjusted that so the lineage can be seen from the point in the tree that the ancestor is related rather than as a root direct from the origin table.

All in all, this script should make more sense to the user than the previous versions.

DECLARE    @StartParentTable    VARCHAR(256);
 
SELECT @StartParentTable = 'tblCampaignMain';
 
WITH Posterity (ParentTable,ParentColumn, ChildTable,ChildColumn, FKLevel, FKGenealogy) 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.FKGenealogy + CAST(CAST(OBJECT_NAME(sfk.referenced_object_id) AS VARCHAR(MAX)) + '.' AS VARCHAR(MAX))
                      ),1,1,'')
      FROM sys.foreign_keys sfk
          INNER Join Posterity F
              ON OBJECT_NAME(sfk.referenced_object_id) = F.ChildTable
              And F.FKGenealogy 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, FKGenealogy) 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
      ,(FKLevel * -1)-1 AS FKLevel
      ,F.FKGenealogy + CAST(CAST(OBJECT_NAME(sfk.Parent_object_id) AS VARCHAR(MAX))+'.' AS VARCHAR(MAX))
  FROM Posterity 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(('.' + FKGenealogy + 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 Posterity)
      And sfk.referenced_object_id <> sfk.parent_object_id
      And F.ChildTable not IN (SELECT ChildTable FROM Posterity)
)
 
SELECT ParentTable,ParentColumn, ChildTable,ChildColumn, FKLevel, FKGenealogy
	,CASE 
		WHEN FKLevel > 0 THEN 'Descendant' 
		WHEN FKLevel = 0 THEN 'Root'
		END AS Direction
FROM Posterity
UNION All
SELECT ParentTable,ParentColumn, ChildTable,ChildColumn, FKLevel, FKGenealogy
	,'Ancestor' AS Direction
FROM Ancestry
  ORDER BY FKGenealogy ASC, Direction DESC
  OPTION (maxrecursion 500);

Table Space – CS Part Deux

Categories: News, Professional, Scripts, SSC
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.

/* 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

Categories: News, Professional, Scripts, SSC
Comments: 1 Comment
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.

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

Categories: News, Professional, Scripts, SSC
Comments: 1 Comment
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:

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.

FK Hierarchy v 2.1

Categories: News, Professional, SSC
Comments: 1 Comment
Published on: August 9, 2011

Last month I published an update to my Foreign Key Hierarchy script.  Today, I am providing a new update for that script.  A friend (Rémi Grégoire) helped out with some mods to this script.  The change for this month is nothing too intrusive.  The script is now updated for those databases that are Case Sensitive.

DECLARE    @StartParentTable    VARCHAR(256)
 
SELECT @StartParentTable = 'my starting table'
 
;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)

This update should make it more usable for any that may be using it or is interested in using it.  Thanks for Rémi for taking the time to propose this update.

Foreign Key Hierarchy Update

Categories: News, Professional, Scripts, SSC
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:

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

and the following in the recursive definition.

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.

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.

Key Discovery III

Categories: News, Professional, Scripts, SSC
Comments: 2 Comments
Published on: February 1, 2010

In Part I and Part II of the series, I discussed documenting and discovering Primary Keys and Clustered Indexes.  In this article I will show how to document table relationships from a hierarchical standpoint.  The type of documentation I will demonstrate in this article will prove insightful and useful – if for nothing more than pure documentation.

I have had the pleasure of sharing this information in a User Group meeting.  That was the first time I had presented at a User’s Group meeting.  I gave that presentation shortly after hammering out the script to help document a database as a part of a project that is still on-going.  For the project, I first set out to find a script that fulfill the requirements for me.  All I could find were scripts similar to what I had already written.  They would present the Tables and foreign keys for those tables as well as the child tables.  The problem with this was that the list was in no particular order.  I then found another script that presented the data in an hierarchy – however it was not a true hierarchy.  I needed a script that could lay out the hierarchy of the foreign keys in the database when provided with one table from the database as the starting point.  This is how I define the perspective.  Now would be a good time to discuss the requirements I had defined for this script.

  1. Document Foreign Key Relationships
  2. Foreign Key Relationships listed in Hierarchical Format
  3. Create Perspectives (View of Hierarchy Tree from Specified Parent Table)
  4. No Cursors or While Loops

Why are these requirements necessary?  This is to help document a database.  I needed to be able to see the ERD at a reasonable size.  By adding the perspective requirement I could take a large database and reduce the number of objects displayed very quickly.  Furthermore, by using a script, I can create the documentation that displays the table relationships very quickly.  This information also helps in knowing the insert and delete order of data in the respective tables as they relate to one another.

The scripts I could find on the net were quickly eliminated due to the requirements.  Most used a looping mechanism, while others did not create a true hierarchy.  Thus I turned to my own information store to develop a script that could traverse the system views and create the kind of report that I needed.

To meet the requirements, I started looking to a CTE.  And yes a recursive CTE at that.  I know – not 100% set-based due to the recursion – but, it is very efficient for this purpose.  In scenarios such as this, this kind of solution is acceptable.  So, starting with the base query:

;With FkCascade AS (
	SELECT OBJECT_NAME(sfk.referenced_object_id) AS ParentTable
	,OBJECT_NAME(sfk.Parent_object_id) AS ChildTable
	,sc.name AS ChildColumn
	,sc2.name AS ParentColumn
	,0 AS FKLevel
	FROM sys.foreign_key_columns sfkc
		INNER JOIN sys.foreign_keys sfk
			ON sfkc.constraint_object_id = sfk.object_id
		INNER JOIN sys.COLUMNS sc
			ON sfkc.parent_object_id = sc.object_id
			AND sfkc.parent_column_id = sc.column_id
		INNER JOIN sys.COLUMNS sc2
			ON sfkc.referenced_object_id = sc2.object_id
			AND sfkc.referenced_column_id = sc2.column_id
	UNION ALL
	SELECT OBJECT_NAME(sfk.referenced_object_id) AS ParentTable
	,OBJECT_NAME(sfk.Parent_object_id) AS ChildTable
	,sc.name AS ChildColumn
	,sc2.name AS ParentColumn, FKLevel + 1
		FROM FkCascade F
		INNER JOIN sys.foreign_keys sfk
			ON f.ChildTable = OBJECT_NAME(sfk.referenced_object_id)
		INNER JOIN sys.foreign_key_columns sfkc
			ON sfkc.constraint_object_id = sfk.object_id
		INNER JOIN sys.COLUMNS sc
			ON sfkc.parent_object_id = sc.object_id
			AND sfkc.parent_column_id = sc.column_id
		INNER JOIN sys.COLUMNS sc2
			ON sfkc.referenced_object_id = sc2.object_id
			AND sfkc.referenced_column_id = sc2.column_id
)
 
SELECT * FROM FKCascade
	ORDER BY ParentTable ASC
	OPTION (maxrecursion 500)

This query gets me pretty close to the desired outcome and only needs a few more tweaks.  The above script more or less does the same sort of thing I saw other scripts doing that I found from internet searches.  The tables and foreign key level are displayed, but no linkage is quickly displayed by the query.  That can be resolved by adding a varbinary to the the query and then concatenating the value with each recursion.

;With FkCascade (ParentTable,ParentColumn, ChildTable,ChildColumn, FKLevel, SortCol) AS (
	SELECT OBJECT_NAME(sfk.referenced_object_id) AS ParentTable
	,sc2.name AS ParentColumn
	,OBJECT_NAME(sfk.Parent_object_id) AS ChildTable
	,sc.name AS ChildColumn
	,0 AS FKLevel,cast(row_number() over(ORDER BY sfk.referenced_object_id) AS varbinary(50)) --varbinary value and sortcol help to create the hierarchy.  Larger trees require a larger varbinary value
	FROM sys.foreign_key_columns sfkc
		INNER JOIN sys.foreign_keys sfk
			ON sfkc.constraint_object_id = sfk.object_id
		INNER JOIN sys.COLUMNS sc
			ON sfkc.parent_object_id = sc.object_id
			AND sfkc.parent_column_id = sc.column_id
		INNER JOIN sys.COLUMNS sc2
			ON sfkc.referenced_object_id = sc2.object_id
			AND sfkc.referenced_column_id = sc2.column_id
	UNION ALL
	SELECT OBJECT_NAME(sfk.referenced_object_id) AS ParentTable
	,sc2.name AS ParentColumn
	,OBJECT_NAME(sfk.Parent_object_id) AS ChildTable
	,sc.name AS ChildColumn
	,FKLevel + 1,cast(SortCol + cast(row_number() over(ORDER BY sfk.referenced_object_id) AS varbinary(3)) AS varbinary(50))
		FROM FkCascade F
		INNER JOIN sys.foreign_keys sfk
			ON f.ChildTable = OBJECT_NAME(sfk.referenced_object_id)
		INNER JOIN sys.foreign_key_columns sfkc
			ON sfkc.constraint_object_id = sfk.object_id
		INNER JOIN sys.COLUMNS sc
			ON sfkc.parent_object_id = sc.object_id
			AND sfkc.parent_column_id = sc.column_id
		INNER JOIN sys.COLUMNS sc2
			ON sfkc.referenced_object_id = sc2.object_id
			AND sfkc.referenced_column_id = sc2.column_id
)
 
SELECT ParentTable,ParentColumn, ChildTable,ChildColumn, FKLevel, SortCol,LEN(sortcol)
FROM FKCascade
	ORDER BY SortCol ASC
	OPTION (maxrecursion 500)

Ok, now the query is very close to what I need.  From the requirements, I only need to alter the script just a bit more in order to be able to create a perspective.  The perspective is achieved through variables and a slight change to the CTE.

DECLARE	@StartParentTable	Varchar(256)
 
SELECT @StartParentTable = 'MyTable'
 
;With FkCascade (ParentTable,ParentColumn, ChildTable,ChildColumn, FKLevel, SortCol) AS (
	SELECT OBJECT_NAME(sfk.referenced_object_id) AS ParentTable
	,sc2.name AS ParentColumn
	,OBJECT_NAME(sfk.Parent_object_id) AS ChildTable
	,sc.name AS ChildColumn
	,0 AS FKLevel,cast(row_number() over(ORDER BY sfk.referenced_object_id) AS varbinary(50))
	FROM sys.foreign_key_columns sfkc
		INNER JOIN sys.foreign_keys sfk
			ON sfkc.constraint_object_id = sfk.object_id
		INNER JOIN sys.COLUMNS sc
			ON sfkc.parent_object_id = sc.object_id
			AND sfkc.parent_column_id = sc.column_id
		INNER JOIN sys.COLUMNS sc2
			ON sfkc.referenced_object_id = sc2.object_id
			AND sfkc.referenced_column_id = sc2.column_id
		WHERE OBJECT_NAME(sfk.referenced_object_id) = @StartParentTable
	UNION ALL
	SELECT OBJECT_NAME(sfk.referenced_object_id) AS ParentTable
	,sc2.name AS ParentColumn
	,OBJECT_NAME(sfk.Parent_object_id) AS ChildTable
	,sc.name AS ChildColumn
	,FKLevel + 1,cast(SortCol + cast(row_number() over(ORDER BY sfk.referenced_object_id) AS varbinary(3)) AS varbinary(50))
		FROM FkCascade F
		INNER JOIN sys.foreign_keys sfk
			ON f.ChildTable = OBJECT_NAME(sfk.referenced_object_id)
		INNER JOIN sys.foreign_key_columns sfkc
			ON sfkc.constraint_object_id = sfk.object_id
		INNER JOIN sys.COLUMNS sc
			ON sfkc.parent_object_id = sc.object_id
			AND sfkc.parent_column_id = sc.column_id
		INNER JOIN sys.COLUMNS sc2
			ON sfkc.referenced_object_id = sc2.object_id
			AND sfkc.referenced_column_id = sc2.column_id
)
 
SELECT ParentTable,ParentColumn, ChildTable,ChildColumn, FKLevel, SortCol
FROM FKCascade
	ORDER BY SortCol ASC
	OPTION (maxrecursion 500)
Go

Through the above script, I am now able to achieve all of the requirements that had been pre-defined.  In testing the query, however, I discovered that the query was not yet quite complete.  There comes a time when there is an additional foreign key defined on the table that does not fit into the above query as a downstream perspective of the origin table.  There may be an occasion where an additional “parent” table needs to be displayed in the query.  Thus I must be able to traverse back upwards now in order to complete the perspective.  This was achieved through the following query.

DECLARE	@StartParentTable	Varchar(256)
 
SELECT @StartParentTable = 'MyTable'
 
;With FkCascade (ParentTable,ParentColumn, ChildTable,ChildColumn, FKLevel, SortCol) AS (
	SELECT OBJECT_NAME(sfk.referenced_object_id) AS ParentTable
	,sc2.name AS ParentColumn
	,OBJECT_NAME(sfk.Parent_object_id) AS ChildTable
	,sc.name AS ChildColumn
	,0 AS FKLevel,cast(row_number() over(ORDER BY sfk.referenced_object_id) AS varbinary(50))
	FROM sys.foreign_key_columns sfkc
		INNER JOIN sys.foreign_keys sfk
			ON sfkc.constraint_object_id = sfk.object_id
		INNER JOIN sys.COLUMNS sc
			ON sfkc.parent_object_id = sc.object_id
			AND sfkc.parent_column_id = sc.column_id
		INNER JOIN sys.COLUMNS sc2
			ON sfkc.referenced_object_id = sc2.object_id
			AND sfkc.referenced_column_id = sc2.column_id
		WHERE OBJECT_NAME(sfk.referenced_object_id) = @StartParentTable
	UNION ALL
	SELECT OBJECT_NAME(sfk.referenced_object_id) AS ParentTable
	,sc2.name AS ParentColumn
	,OBJECT_NAME(sfk.Parent_object_id) AS ChildTable
	,sc.name AS ChildColumn
	,FKLevel + 1,cast(SortCol + cast(row_number() over(ORDER BY sfk.referenced_object_id) AS varbinary(3)) AS varbinary(50))
		FROM FkCascade F
		INNER JOIN sys.foreign_keys sfk
			ON f.ChildTable = OBJECT_NAME(sfk.referenced_object_id)
		INNER JOIN sys.foreign_key_columns sfkc
			ON sfkc.constraint_object_id = sfk.object_id
		INNER JOIN sys.COLUMNS sc
			ON sfkc.parent_object_id = sc.object_id
			AND sfkc.parent_column_id = sc.column_id
		INNER JOIN sys.COLUMNS sc2
			ON sfkc.referenced_object_id = sc2.object_id
			AND sfkc.referenced_column_id = sc2.column_id
), WalkBackUp (ParentTable,ParentColumn, ChildTable,ChildColumn, FKLevel, SortCol) AS (
	SELECT DISTINCT OBJECT_NAME(sfk.referenced_object_id) AS ParentTable
	,sc2.name AS ParentColumn
	,OBJECT_NAME(sfk.Parent_object_id) AS ChildTable
	,sc.name AS ChildColumn
	,-1 AS FKLevel,cast(SortCol + cast(row_number() over(ORDER BY sfk.referenced_object_id) AS varbinary(3)) AS varbinary(50))
		FROM FkCascade 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
			INNER JOIN sys.COLUMNS sc
				ON sfkc.parent_object_id = sc.object_id
				AND sfkc.parent_column_id = sc.column_id
			INNER JOIN sys.COLUMNS sc2
				ON sfkc.referenced_object_id = sc2.object_id
				AND sfkc.referenced_column_id = sc2.column_id
	UNION ALL
	SELECT OBJECT_NAME(sfk.referenced_object_id) AS ParentTable
	,sc2.name AS ParentColumn
	,OBJECT_NAME(sfk.Parent_object_id) AS ChildTable
	,sc.name AS ChildColumn
	,f.FKLevel -1,cast(SortCol + cast(row_number() over(ORDER BY sfk.referenced_object_id) AS varbinary(3)) AS varbinary(50))
		FROM WalkBackup 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
			INNER JOIN sys.COLUMNS sc
				ON sfkc.parent_object_id = sc.object_id
				AND sfkc.parent_column_id = sc.column_id
			INNER JOIN sys.COLUMNS sc2
				ON sfkc.referenced_object_id = sc2.object_id
				AND sfkc.referenced_column_id = sc2.column_id
		WHERE f.parentTable NOT IN (SELECT ParentTable FROM FKCascade)
)
 
SELECT ParentTable,ParentColumn, ChildTable,ChildColumn, FKLevel, SortCol
FROM FKCascade
UNION ALL
SELECT ParentTable,ParentColumn, ChildTable,ChildColumn, FKLevel, SortCol
FROM WalkBackUp
	ORDER BY SortCol ASC
	OPTION (maxrecursion 500)

This query can easily be modified to pull back just the unique table names that are found in the CTE.  Why would that be necesary?  That would provide an easy method to pull back unique tables in the event that an ERD is to be derived from the perspective.  I didn’t delve too much into any of the scripts presented here.  At this point in the series, the only complexity that may need to be explained is the recursive piece of the scripts.  I think there are plenty of articles on that very subject.  The main goal here is to show that the documentation of FKs can be made relatively simple as well as provide plenty of insight into the database.  Test the queries.

I am including the scripts and the execution plan for the final script.  Included in the scripts will be the presentation given on the same subject.  The inclusion of that slide deck is a secondary reason for not getting into great detail in this post.  You can download it here.

The final blog in this particular series will be very short. I will cover the need for indexes on FKs. This is another topic that has been recently discussed. I will explore a couple of different scripts and compare and contrast those scripts performance.

page 1 of 1




Calendar
April 2014
M T W T F S S
« Mar    
 123456
78910111213
14151617181920
21222324252627
282930  
Content
SQLHelp

SQLHelp


Welcome , today is Thursday, April 24, 2014