jul
10
2018

T-SQL Tuesday #104 – Encontrar tablas y columnas en SQL Server

tsql2sday150x150

 Esta publicación forma parte de T-SQL Tuesday, que es una fiesta de blog mensual el segundo martes de cada mes. Todos el mundo es bienvenido y tiene la oportunidad de escribir sobre SQL Server.

El anfitrión de este mes es Bert Wagner (B|T) y el tema es “Código sin el cuál odiaría vivir”.

 
 
Antecedentes

Muy a menudo necesitamos encontrar algo relacionado con las tablas de nuestras base de datos, esto podría ser todas las tablas de una base de datos, las tablas que tienen una columna específica, el tamaño, el uso o muchas otras cosas.

No tardé mucho desde que me convertí en DBA a tiempo completo hasta que decidí escribir este script, que he actualizado bastante, añadiendo pequeños arreglos para aumentar el número de casos de uso.

El procedimiento acepta los siguientes parámetros:

  • @dbname, el nombre de la base de datos que queremos examinar, acepta NULL para todas las bases de datos en la instancia actual y comodines
  • @schemaName, el nombre del esquema que queremos examinar, acepta NULL para todos los esquemas y comodines
  • @tableName, el nombre de la tabla que estamos buscando, acepta NULL para todas las tablas y comodines
  • @columnName, el nombre de la columna que estamos buscando, acepta NULL para todas las columnas y comodines. Establecerá @onlyTablesList a 0
  • @onlyTablesList, Devolverá solo la información de la tabla si 1, de lo contrario, todas las tablas y columnas que coinciden con los filtros

 
Información útil que proporciona a nivel de tabla:

  • tableType, para identificar tablas HEAP
  • row_count, para identificar tablas con muchas filas o sin filas en absoluto
  • TotalSpaceMB, para identificar tablas grandes en espacio en disco
  • LastUserAccess, para identificar tablas que no se usan
  • TotalUserAccess, para identificar tablas que son muy utilizadas
  • TableTriggers, para identificar tablas que tienen desencadenantes

 
Información útil que proporciona a nivel de columns:

  • DataType-Size, para identificar tipos de datos sobredimensionados, incorrectos o en desuso
  • Identity, para identificar columnas identity
  • Mandatory-DefaultValue, para identificar columnas NULL/NOT NULL o con valores por defecto
  • PrimaryKey, para identificar las columnas de clave principal
  • Collation, para identificar columnas que pueden tener diferentes intercalaciones de la base de datos
  • ForeignKey-ReferencedColumn, para identificar las claves externas y la tabla.columna a la que hacen referencia

 
Como podeis ver, el procedimiento es muy versátil y puede utilizarse por muchas razones, desde obtener una primera vistazo de una nueva base de datos hasta para obtener una visión completa de cada campo y la relación entre las diferentes tablas si están definidas.

 
El procedimiento

Entonces, después de haber visto para qué puede usarlo y cómo, es hora de ver el código. Podéis agregarlo a vuestra propia base de datos de DBA o [master] si lo prefiere.

 

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. 
-- 
-- Assupmtions:	Depending on the user executing the SP, the metadata displayed can vary 
--				User 'reporting' is granted with view definition permission to guarantee all info is complete in the report server 
-- 
-- 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

 
Conclusión

Espero que hayais encontrado útil este script y os recomiendo que os mantengais atentos porque pronto voy a anunciar algo importante.

Quiero terminar esta publicación agradeciendo a Bert por organizar el evento de este mes y elegir un tema tan bueno.

Creo que la comunidad de SQL Server es uno de los puntos más fuertes de la plataforma de datos de Microsoft gracias a la gran cantidad de personas que comparten su conocimiento y tiempo para que otros crezcan como profesionales.

¡Gracias!
 

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *