Jul
10
2018

T-SQL Tuesday #104 – Finding tables and columns in SQL Server

tsql2sday150x150

 This post is part of the T-SQL Tuesday, which is a monthly blog party on the second Tuesday of each month. Everyone is welcome and have the chance to write about SQL Server.
This month’s host is Bert Wagner (B|T) and the topic is “Code You Would Hate To Live Without”.

 
 
Background

Very so often we need to find something related to our database tables, this might be all tables in a database, tables that have specific column, size, usage, you name it.

Not later than I became a full time DBA, I decided to write this script which I have been updating quite a lot, adding small bits to increase the number of use cases.

The procedure accepts the following parameters:

  • @dbname, the name of the database we want to look into, it accepts NULL for all databases in the current instance and wildcards
  • @schemaName, the name of the schema we want to look into, it accepts NULL for all schemas and wildcards
  • @tableName, the name of the table we are looking for, it accepts NULL for all tables and wildcards
  • @columnName, the name of the column we are looking for, it accepts NULL for all columns and wildcards. It will set @onlyTablesList to 0
  • @onlyTablesList, It will return only table information if 1, otherwise all tables and columns that match the filters

 
Useful information it provides at table level:

  • tableType, to identify HEAP tables
  • row_count, to identify tables with plenty of rows or now rows at all
  • TotalSpaceMB, to identify big tables in size
  • LastUserAccess, to identify tables that are not used
  • TotalUserAccess, to identify tables that are heavily used
  • TableTriggers, to identify tables that have triggers

 
Useful information it provides at column level:

  • DataType-Size, to identify supersized, incorrect or deprecated data types
  • Identity, to identify identity columns
  • Mandatory-DefaultValue, to identify NULL/NOT NULL columns or with default constraints
  • PrimaryKey, to identify primary key columns
  • Collation, to identify columns that might have different collation from the database
  • ForeignKey-ReferencedColumn, to identify foreign keys and the table.column they reference

 
So as you can see the procedure is very versatile and can be used for a number of reasons, from getting a first glance of a new database to get a full understanding of every field and the relationship between the different tables if defined.

 
The procedure

So after you have seen what you can use it for and how, it is time to see the code. You can add it to your own DBA database or master if you prefer.

 

USE [master]
GO
IF OBJECT_ID ('[dbo].[sqlg_tableDescription]') IS NULL EXECUTE sp_executesql N'CREATE PROCEDURE [dbo].[sqlg_tableDescription] AS SELECT 1 AS col1'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
--	Copyright 2018 Raul Gonzalez @SQLDoubleG
-- 
-- Permission is hereby granted, free of charge, to any person obtaining a copy of this software 
-- and associated documentation files (the "Software"), to deal in the Software without restriction, 
-- including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, 
-- and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so,
-- subject to the following conditions:
--
-- The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.
--
-- THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, 
-- INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. 
-- IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY,
-- WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE 
-- OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
--
--
-- Author:		Raul Gonzalez 
-- Create date: 17/07/2013 
-- Description:	Returns descriptive information for a database, schema or table specified. 
-- 
-- Change Log:	24/09/2013 RAG Added column row_count for tables 
--				25/10/2013 RAG Changed the calculation of row_count and added TotalSpaceMB, UsedSpaceMB and UnusedSpaceMB for tables 
--				28/10/2013 RAG Added filter to not process Objects created by an internal SQL Server component (is_ms_shipped = 0) 
--				29/10/2013 RAG Added parameter column name, if provided, only tables which match the column name will appear on the list 
--				29/10/2013 RAG @tableName and @columnName can come with wildcards as the comparison use LIKE  
--				29/10/2013 RAG included column Collation  
--				14/11/2013 RAG Changed the comparison of database name to like, this way we can call the SP with database name 'dbName%' 
--				02/12/2013 RAG Added column IsIdentity 
--				11/12/2013 RAG Added a new resultset for when @columnName is specified 
--				13/12/2013 RAG Added a DataSpace column 
--				28/02/2014 RAG Change on how data sizes are calculated to get same results as Table - properties - storage, also included Index Size 
--				29/10/2014 RAG Added Columns LastUserAccess and TotalUserAccess aggregating data from sys.dm_db_index_usage_stats 
--				09/03/2015 RAG Added Columns tableType, HEAP or CLUSTERED 
--				30/03/2015 RAG Changed the way of counting rows to consider partitioned tables 
--				01/05/2015 RAG Use total_pages to calculate total size 
--				22/03/2016 RAG Added indexed views to the results 
--								Create temp table for columns using SELECT INTO as it is shorter and have the same definition 
--				13/04/2016 RAG Changed the way [IndexSpaceUsed] is calculated to display total size of non clustered indexes 
--				14/07/2016 SZO Removed no longer necessary comments from the code block. 
--				04/10/2016 RAG Changes due to case sensitivity (column definition) 
--				15/02/2017 RAG Changed table type to include Clustered columnstore by getting indexes.type_desc instead of hardcoded HEAP and CLUSTERED 
--				 
-- ============================================= 
ALTER PROCEDURE [dbo].[sqlg_tableDescription]
	@dbname				SYSNAME = NULL 
	, @schemaName		SYSNAME = NULL 
	, @tableName		SYSNAME = NULL 
	, @columnName		SYSNAME = NULL 
	, @onlyTablesList	BIT		= 0 
AS 
BEGIN 
	 
	SET NOCOUNT ON 
	 
	IF @columnName IS NOT NULL BEGIN SET @onlyTablesList = 0 END 

	DECLARE @StringSQLVersion	NVARCHAR(128)	= CONVERT(NVARCHAR(128),SERVERPROPERTY('ProductVersion'))
	DECLARE @NumericSQLVersion	DECIMAL(3,1)	= CONVERT(DECIMAL(3,1), (LEFT( @StringSQLVersion,  CHARINDEX('.', @StringSQLVersion, 0) + 1 )) )

	CREATE TABLE #resultTables 
		( databaseName		SYSNAME			NULL 
		, schemaName		SYSNAME			NULL 
		, tableName			SYSNAME			NULL 
		, tableType			SYSNAME			NULL 
		, row_count			INT				NULL 
		, TotalSpaceMB		DECIMAL(15,3)	NULL 
		, DataSpaceMB		DECIMAL(15,3)	NULL 
		, IndexSpaceMB		DECIMAL(15,3)	NULL 
		, UnusedSpaceMB		DECIMAL(15,3)	NULL 
		, TableTriggers		VARCHAR(500)	NULL 
		, LastUserAccess	DATETIME		NULL 
		, TotalUserAccess	BIGINT			NULL 
		, LastUserLookup	DATETIME		NULL 
		, Column_id			INT				NULL 
		, columnName		SYSNAME			NULL 
		, DataType			SYSNAME			NULL 
		, Size				VARCHAR(30)		NULL 
		, IsIdentity		VARCHAR(3)		NULL 
		, Mandatory			VARCHAR(3)		NULL 
		, DefaultValue		NVARCHAR(MAX)	NULL 
		, PrimaryKey		VARCHAR(3)		NULL 
		, ForeignKey		VARCHAR(3)		NULL 
		, IsComputed		VARCHAR(3)		NULL 
		, Collation			SYSNAME			NULL 
		, [definition]		NVARCHAR(MAX)	NULL 
		, Filestream		VARCHAR(3)		NULL 
		, ReferencedColumn	SYSNAME			NULL 
		, TableDescription	SQL_VARIANT 
		, ColDescription	SQL_VARIANT) 
	 
	SELECT *  
		INTO #resultColumns 
		FROM #resultTables 
		WHERE 1=0 
 
	CREATE TABLE #db_triggers 
		( parent_id			INT 
		, trigger_type		NVARCHAR(4000)) 
	 
	CREATE TABLE #index_usage_stats 
		( object_id			INT 
		, last_user_access	DATETIME		NULL 
		, total_user_access	BIGINT			NULL) 
 
	DECLARE @databases TABLE  
		( ID				INT IDENTITY 
		, dbname			SYSNAME) 
		 
	DECLARE @sql			NVARCHAR(MAX) 
			, @countDBs		INT = 1 
			, @numDBs		INT 
 
	INSERT INTO @databases  
		SELECT TOP 100 PERCENT name  
			FROM sys.databases  
			WHERE [name] NOT IN ('model', 'tempdb')  
				AND state = 0  
				AND name LIKE ISNULL(@dbname, name) 
			ORDER BY name ASC		 
 
	SET @numDBs = @@ROWCOUNT 
 
	WHILE @countDBs <= @numDBs BEGIN 
		 
		SELECT @dbname = dbname  
			FROM @databases 
			WHERE ID = @countDBs 
 
		SET @sql = N' 
		 
			USE ' + QUOTENAME(@dbname) + N' 
 
			DECLARE @schemaName SYSNAME = ' + ISNULL('''' + @schemaName+ '''','NULL')  + N' 
			DECLARE @tableName	SYSNAME = ' + ISNULL('''' + @tableName + '''','NULL')  + CONVERT(NVARCHAR(MAX), N' 
 
			TRUNCATE TABLE #db_triggers 
			TRUNCATE TABLE #index_usage_stats 
 
			INSERT INTO #db_triggers 
			SELECT parent_id 
					, name + '' ('' + 
						CASE WHEN tr.is_instead_of_trigger = 1 THEN ''INSTEAD OF '' ELSE ''AFTER '' END + 
						STUFF( (SELECT '', '' + type_desc FROM sys.trigger_events AS tre WHERE tre.object_id = tr.object_id ORDER BY type_desc ASC FOR XML PATH('''')  ),1, 2, '''') + 
						CASE WHEN tr.is_disabled = 1 THEN '' (Disabled)'' ELSE '''' END + '')'' 
						AS trigger_type				 
				FROM sys.triggers AS tr 
 
			;WITH t AS( 
				SELECT object_id, [last_user_lookup] AS last_user_access , ( ius.user_scans + ius.user_seeks + ius.user_lookups ) AS total_user_access 
					FROM sys.dm_db_index_usage_stats AS ius WHERE database_id = DB_ID() 
				UNION ALL	 
				SELECT object_id, [last_user_scan] , 0 -- Zero because we just have to count user access once, not for each date!! 
					FROM sys.dm_db_index_usage_stats AS ius WHERE database_id = DB_ID() 
				UNION ALL	 
				SELECT object_id, [last_user_seek] , 0 -- Zero because we just have to count user access once, not for each date!! 
					FROM sys.dm_db_index_usage_stats AS ius WHERE database_id = DB_ID() 
			)  
			INSERT INTO #index_usage_stats 
				SELECT object_id, MAX(last_user_access), SUM(CONVERT(BIGINT,total_user_access))  FROM t 
					GROUP BY object_id 
							 
			INSERT INTO #resultTables 
					(databaseName 
					, schemaName 
					, tableName 
					, tableType 
					, row_count 
					, TotalSpaceMB 
					, DataSpaceMB 
					, IndexSpaceMB 
					, UnusedSpaceMB 
					, Column_id 
					, TableDescription 
					, TableTriggers 
					, LastUserAccess 
					, TotalUserAccess) 
			SELECT DB_NAME() 
					, OBJECT_SCHEMA_NAME(i.object_id)  
					, OBJECT_NAME(i.object_id)  
					, CASE  
							WHEN OBJECTPROPERTYEX(i.object_id, ''IsView'') = 1 THEN ''INDEXED VIEW''  
							ELSE i.type_desc 
						END AS tableType  
 
					 
					, SUM(CASE WHEN a.type = 1 THEN p.rows ELSE 0 END)  
					, CONVERT( DECIMAL(15,3), ISNULL( (8.000000 * SUM(a.total_pages)) / 1024 , 0 ) ) AS [TotalSpaceUsed] 
					, CONVERT( DECIMAL(15,3), ISNULL( (8.000000 * SUM(               CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)) / 1024 , 0 ) ) AS [DataSpaceUsed] 
					 
					-- wrong calculation, use OUTER APPLY 
					--, CONVERT( DECIMAL(15,3), ISNULL( (8.000000 * SUM(a.used_pages - CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)) / 1024 , 0 ) ) AS [IndexSpaceUsed] 
					, CONVERT( DECIMAL(15,3), ixsp.[IndexSpaceUsed] / 1024. ) AS [IndexSpaceUsed] 
 
 
					--, CONVERT( DECIMAL(9,2), (SUM(a.total_pages) * 8) / 1024. ) AS TotalSpaceMB 
					--, CONVERT( DECIMAL(9,2), (SUM(a.data_pages) * 8) / 1024. ) AS DataSpaceMB 
					--, CONVERT( DECIMAL(9,2), (SUM(a.used_pages)  * 8) / 1024. ) AS UsedSpaceMB 
					, CONVERT( DECIMAL(9,2), ((SUM(a.total_pages) - SUM(a.used_pages)) * 8 / 1024.) )  AS UnusedSpaceMB 
 
					, 0 
					, xp.value 
					, STUFF( (SELECT '', '' + trigger_type FROM #db_triggers AS tr WHERE tr.parent_id = i.object_id FOR XML PATH('''')  ),1, 2, '''')  AS triggers 
					, ius.last_user_access 
					, total_user_access 
				FROM sys.indexes AS i  
					INNER JOIN sys.partitions AS p 
						ON p.object_id = i.object_id 
							AND p.index_id = i.index_id 
					INNER JOIN sys.allocation_units AS a  
						ON a.container_id = p.partition_id  
					LEFT JOIN sys.extended_properties AS xp 
						ON xp.major_id = i.object_id 
							AND xp.minor_id = 0 
							AND xp.name = ''MS_Description'' 
 
					LEFT JOIN #index_usage_stats AS ius 
						ON ius.object_id = i.object_id  
					OUTER APPLY (
						SELECT  
								') +  CASE WHEN @NumericSQLVersion > 11.0 THEN CONVERT(NVARCHAR(MAX), ' 
								CASE WHEN (tbl.is_memory_optimized=0) THEN ') ELSE CONVERT(NVARCHAR(MAX),'') END  
							+ CONVERT(NVARCHAR(MAX), ' 
									ISNULL(( 
									(SELECT SUM (used_page_count) FROM sys.dm_db_partition_stats ps WHERE ps.object_id = tbl.object_id) 
									+ ( CASE (SELECT count(*) FROM sys.internal_tables WHERE parent_id = tbl.object_id AND internal_type IN (202,204,207,211,212,213,214,215,216,221,222)) 
										WHEN 0 THEN 0 
										ELSE ( 
										  SELECT sum(p.used_page_count) 
										  FROM sys.dm_db_partition_stats p, sys.internal_tables it 
										  WHERE it.parent_id = tbl.object_id AND it.internal_type IN (202,204,207,211,212,213,214,215,216,221,222) AND p.object_id = it.object_id) 
										END ) 
									- (SELECT SUM (CASE WHEN(index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count) ELSE 0 END) 
									   FROM sys.dm_db_partition_stats WHERE object_id = tbl.object_id) 
									) * 8, 0.0) 
							') +  CASE WHEN @NumericSQLVersion > 11.0 THEN CONVERT(NVARCHAR(MAX),' 
								else 
									isnull((select (tms.[memory_used_by_indexes_kb]) 
									from [sys].[dm_db_xtp_table_memory_stats] tms 
									where tms.object_id = tbl.object_id), 0.0) 
								end') ELSE CONVERT(NVARCHAR(MAX),'') END  
							+ CONVERT(NVARCHAR(MAX), ' 
							   AS [IndexSpaceUsed]
						FROM sys.tables AS tbl 
						WHERE tbl.object_id = i.object_id 
					) AS ixsp  
				WHERE OBJECTPROPERTYEX(i.object_id, ''IsMSShipped'') = 0 
					AND i.index_id IN (0, 1) -- Heap or Clustered 
					AND OBJECT_SCHEMA_NAME(i.object_id) = ISNULL(@schemaName, OBJECT_SCHEMA_NAME(i.object_id)) 
					AND OBJECT_NAME(i.object_id) LIKE ISNULL(@tableName, OBJECT_NAME(i.object_id)) 
				GROUP BY i.object_id 
						--, t.name 
						, i.index_id 
						, i.type_desc 
						, ixsp.[IndexSpaceUsed] 
						--, p.rows 
						, xp.value 
						, ius.last_user_access 
						, total_user_access 
		') 
		PRINT @sql 
		-- Insert all tables with their descriptions 
		EXECUTE sp_executesql @sql 
	 
		IF @onlyTablesList = 0 BEGIN  
 
			SET @sql = N' 
			 
				USE ' + QUOTENAME(@dbname) + N' 
 
				DECLARE @schemaName SYSNAME = ' + ISNULL('''' + @schemaName+ '''','NULL')  + N' 
				DECLARE @tableName	SYSNAME = ' + ISNULL('''' + @tableName + '''','NULL')  + N' 
				DECLARE @columnName	SYSNAME = ' + ISNULL('''' + @columnName + '''','NULL')  + N' 
			 
				INSERT INTO #resultColumns 
						(databaseName 
						, schemaName 
						, tableName 
						, Column_id 
						, columnName 
						, DataType 
						, Size 
						, IsIdentity 
						, Mandatory 
						, DefaultValue 
						, PrimaryKey 
						, ForeignKey 
						, IsComputed 
						, Collation 
						, [definition] 
						, Filestream 
						, ReferencedColumn 
						, TableDescription 
						, ColDescription) 
				SELECT DB_NAME() 
						, OBJECT_SCHEMA_NAME(i.object_id) 
						, OBJECT_NAME(i.object_id) 
						, c.column_id 
						, c.name 
						, UPPER(ty.name) 
						, CASE  
								WHEN ty.user_type_id IN (165,167,231) AND c.max_length = -1 THEN ''Unlimited'' 
								WHEN ty.user_type_id IN (231,239) THEN CONVERT(VARCHAR,c.max_length/2) 
								WHEN ty.user_type_id IN (165,167,173,175,231,239) THEN CONVERT(VARCHAR,c.max_length) 
								ELSE '''' 
							END 
						, CASE WHEN c.is_identity = 1 THEN ''Yes''  
								ELSE '''' 
							END 
						, CASE  
								WHEN c.is_nullable = 1 THEN ''No''  
								ELSE ''Yes'' 
							END 
						, CASE  
								WHEN df.definition IS NOT NULL THEN SUBSTRING(df.definition, 2, LEN(df.definition)-2) -- to remove extra parenthesis 
								ELSE '''' 
							END 
						, CASE WHEN EXISTS (SELECT 1  
												FROM sys.index_columns AS ixc  
													LEFT JOIN sys.indexes AS ix 
														ON ix.object_id = i.object_id 
															AND ix.index_id = ixc.index_id 
												WHERE ixc.object_id = i.object_id  
													AND ixc.column_id = c.column_id  
													AND ix.is_primary_key = 1 ) THEN ''Yes'' 
								ELSE '''' 
							END 
						, CASE  
								WHEN fk.object_id IS NOT NULL THEN ''Yes'' 
								ELSE '''' 
							END 
						, CASE  
							WHEN c.is_computed = 1 THEN ''Yes''  
							ELSE '''' 
						END 
						, c.Collation_name 
						, cc.definition 
						, CASE  
							WHEN c.is_filestream = 1 THEN ''Yes''  
							ELSE '''' 
						END 
						, ISNULL(OBJECT_SCHEMA_NAME(rc.object_id) + ''.'' + OBJECT_NAME(rc.object_id) + ''.'' + rc.name, '''') 
						, NULL 
						, xp.value 
					FROM sys.indexes AS i 
						INNER JOIN sys.columns AS c 
							ON c.object_id = i.object_id 
								AND i.index_id IN (0,1) 
						LEFT JOIN sys.types AS ty 
							ON ty.user_type_id = c.user_type_id 
						LEFT JOIN sys.default_constraints AS df 
							ON df.parent_object_id = c.object_id 
								AND df.parent_column_id = c.column_id 
						LEFT JOIN sys.computed_columns AS cc 
							ON cc.object_id = i.object_id 
								AND cc.column_id = c.column_id 
						LEFT JOIN sys.foreign_key_columns AS fkc 
							ON fkc.parent_object_id = c.object_id 
								AND fkc.parent_column_id = c.column_id 
						LEFT JOIN sys.foreign_keys AS fk 
							ON fk.object_id = fkc.constraint_object_id 
						LEFT JOIN sys.columns AS rc 
							ON rc.object_id = fkc.referenced_object_id 
								AND rc.column_id = fkc.referenced_column_id 
						LEFT JOIN sys.extended_properties AS xp 
							ON xp.major_id = i.object_id 
								AND xp.minor_id = c.column_id  
								AND xp.name = ''MS_Description'' 
					WHERE OBJECTPROPERTYEX(i.object_id, ''IsMSShipped'') = 0 
						AND OBJECT_SCHEMA_NAME(i.object_id) = ISNULL(@schemaName, OBJECT_SCHEMA_NAME(i.object_id)) 
						AND OBJECT_NAME(i.object_id) LIKE ISNULL(@tableName, OBJECT_NAME(i.object_id)) 
						AND c.name LIKE ISNULL(@columnName, c.name) 
			' 
			--PRINT @sql 
			-- Insert all Columns for all tables			 
			EXECUTE sp_executesql @sql 
 
		END -- IF @onlyTablesList = 0  
 
		SET @countDBs += 1	 
	END 
 
 
	IF @onlyTablesList = 1 BEGIN 
		SELECT ISNULL(databaseName,'') AS databaseName 
				, ISNULL(schemaName,'') AS schemaName 
				, ISNULL(tableName,'') AS tableName 
				, ISNULL(tableType,'') AS tableType 
				, ISNULL(CONVERT(VARCHAR,row_count), '') AS row_count 
				, ISNULL(CONVERT(VARCHAR,TotalSpaceMB), '') AS TotalSpaceMB 
				, ISNULL(CONVERT(VARCHAR,DataSpaceMB), '') AS DataSpaceMB 
				, ISNULL(CONVERT(VARCHAR,IndexSpaceMB), '') AS IndexSpaceMB 
				, ISNULL(CONVERT(VARCHAR,UnusedSpaceMB), '') AS UnusedSpaceMB 
				, ISNULL(CONVERT(VARCHAR,LastUserAccess, 113), '') AS LastUserAccess 
				, ISNULL(CONVERT(VARCHAR,TotalUserAccess), '') AS TotalUserAccess 
				, ISNULL(TableTriggers,'') AS TableTriggers 
				, ISNULL(TableDescription,'') AS TableDescription 
			FROM #resultTables 
			ORDER BY databaseName 
				, schemaName 
				, tableName 
	END  
	ELSE BEGIN 
 
	-- Delete tables where the columName provided was not found 
		DELETE rt 
			FROM #resultTables AS rt 
				LEFT JOIN #resultColumns AS rc 
					ON rc.databaseName = rt.databaseName 
						AND rc.tableName = rt.tableName 
			WHERE rc.databaseName IS NULL 
				AND ISNULL(@columnName, '') <> '' 
 
		--IF @columnName IS NULL BEGIN 
			;WITH cte AS( 
				SELECT databaseName 
						, schemaName 
						, tableName 
						, tableType 
						, row_count 
						, TotalSpaceMB 
						, DataSpaceMB 
						, IndexSpaceMB 
						, UnusedSpaceMB 
						, LastUserAccess 
						, TotalUserAccess 
						, TableTriggers 
						, columnName 
						, Column_id 
						, DataType 
						, Size 
						, IsIdentity 
						, Mandatory 
						, DefaultValue 
						, PrimaryKey 
						, IsComputed 
						, Collation 
						, [definition] 
						, ForeignKey 
						, ReferencedColumn 
						, [Filestream]			 
						, TableDescription 
						, ColDescription 
					FROM #resultTables 
				UNION ALL 
				SELECT databaseName 
						, schemaName 
						, tableName 
						, tableType 
						, row_count 
						, TotalSpaceMB 
						, DataSpaceMB 
						, IndexSpaceMB 
						, UnusedSpaceMB 
						, LastUserAccess 
						, TotalUserAccess 
						, TableTriggers 
						, columnName 
						, Column_id 
						, DataType 
						, Size 
						, IsIdentity 
						, Mandatory 
						, DefaultValue 
						, PrimaryKey 
						, IsComputed 
						, Collation 
						, [definition] 
						, ForeignKey 
						, ReferencedColumn 
						, [Filestream] 
						, TableDescription 
						, ColDescription 
					FROM #resultColumns 
			) 
			SELECT ISNULL(databaseName,'') AS databaseName 
					, ISNULL(schemaName,'') AS schemaName 
					, ISNULL(tableName,'') AS tableName 
					, ISNULL(tableType,'') AS tableType 
					, ISNULL(CONVERT(VARCHAR,row_count),'') AS row_count 
					, ISNULL(CONVERT(VARCHAR,TotalSpaceMB),'') AS TotalSpaceMB 
					, ISNULL(CONVERT(VARCHAR,DataSpaceMB),'') AS DataSpaceMB 
					, ISNULL(CONVERT(VARCHAR,IndexSpaceMB),'') AS IndexSpaceMB 
					, ISNULL(CONVERT(VARCHAR,UnusedSpaceMB),'') AS UnusedSpaceMB 
					, ISNULL(CONVERT(VARCHAR,LastUserAccess, 113), '') AS LastUserAccess 
					, ISNULL(CONVERT(VARCHAR,TotalUserAccess), '') AS TotalUserAccess 
					, ISNULL(TableTriggers,'') AS TableTriggers 
					, ISNULL(columnName,'') AS columnName 
					, ISNULL(DataType,'') AS DataType 
					, ISNULL(Size,'') AS Size 
					, ISNULL(IsIdentity,'') AS IsIdentity 
					, ISNULL(Mandatory,'') AS Mandatory 
					, ISNULL(DefaultValue,'') AS DefaultValue 
					, ISNULL(PrimaryKey,'') AS PrimaryKey 
					, ISNULL(IsComputed,'') AS IsComputed 
					, ISNULL(Collation,'') AS Collation 
					, ISNULL([definition],'') AS [Definition] 
					, ISNULL(ForeignKey,'') AS ForeignKey 
					, ISNULL(ReferencedColumn,'') AS ReferencedColumn 
					, ISNULL(Filestream,'') AS Filestream			 
					, ISNULL(TableDescription,'') AS TableDescription 
					, ISNULL(ColDescription,'') AS ColDescription 
				FROM cte 
				ORDER BY databaseName 
					, schemaName 
					, tableName 
					, Column_id 
	 
	END 
	DROP TABLE #resultTables 
	DROP TABLE #resultColumns 
	DROP TABLE #db_triggers 
	DROP TABLE #index_usage_stats 
	 
END 
GO

 
Conclusion

Hopefully you found this script useful and I’d recommend you to stay tuned because something big is to be announce from me soon.

I want to finish this post saying a big thank you to Bert for hosting this month’s event and choosing such great topic.

I believe the SQL Server community is one of the strongest points in the Microsoft Data Platform thanks to that many people who are sharing their knowledge and time for others to grow as professionals.

Thanks for reading!

 

Leave a Reply

Your email address will not be published. Required fields are marked *