{"id":550,"date":"2018-07-10T13:13:35","date_gmt":"2018-07-10T12:13:35","guid":{"rendered":"https:\/\/sqldoubleg.live-website.com\/?p=550"},"modified":"2022-12-25T10:13:04","modified_gmt":"2022-12-25T10:13:04","slug":"t-sql-tuesday-104-finding-tables-and-columns","status":"publish","type":"post","link":"https:\/\/www.sqldoubleg.com\/es\/2018\/07\/10\/t-sql-tuesday-104-finding-tables-and-columns\/","title":{"rendered":"T-SQL Tuesday #104 &#8211; Encontrar tablas y columnas en SQL Server"},"content":{"rendered":"<p>En este post quiero compartir un script que utilizo bastante a menudo por su versatilidad&nbsp;<a href=\"https:\/\/bertwagner.com\/2018\/07\/03\/code-youd-hate-to-live-without-t-sql-tuesday-104-invitation\/\" target=\"blank\" rel=\"noopener noreferrer\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/sqldoubleg.live-website.com\/wp-content\/uploads\/2016\/07\/tsql2sday150x150.jpg\" alt=\"tsql2sday150x150\" width=\"154\" height=\"154\" style=\"float:left;margin-right:7px\" \/><\/a><\/p>\n<p>&nbsp;Esta publicaci\u00f3n forma parte de <a href=\"http:\/\/tsqltuesday.com\/\" target=\"_blank\" rel=\"noopener noreferrer\">T-SQL Tuesday<\/a>, 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. <a href=\"https:\/\/bertwagner.com\/2018\/07\/03\/code-youd-hate-to-live-without-t-sql-tuesday-104-invitation\/\" target=\"_blank\" rel=\"noopener noreferrer\"><\/a><\/p>\n<p>El anfitri\u00f3n de este mes es Bert Wagner (<a href=\"https:\/\/bertwagner.com\/\" target=\"_blank\" rel=\"noopener noreferrer\">B<\/a>|<a href=\"https:\/\/twitter.com\/bertwagner\" target=\"_blank\" rel=\"noopener noreferrer\">T<\/a>) y el tema es \u00abC\u00f3digo sin el cu\u00e1l odiar\u00eda vivir\u00bb. <\/p>\n<p>&nbsp;<br \/>\n&nbsp;<br \/>\n<strong>Antecedentes<\/strong><\/p>\n<p>Muy a menudo necesitamos encontrar algo relacionado con las tablas de nuestras base de datos, esto podr\u00eda ser todas las tablas de una base de datos, las tablas que tienen una columna espec\u00edfica, el tama\u00f1o, el uso o muchas otras cosas.<\/p>\n<p>No tard\u00e9 mucho desde que me convert\u00ed en DBA a tiempo completo hasta que decid\u00ed escribir este script, que he actualizado bastante, a\u00f1adiendo peque\u00f1os arreglos para aumentar el n\u00famero de casos de uso.<\/p>\n<p>El procedimiento acepta los siguientes par\u00e1metros:<\/p>\n<ul>\n<li><strong>@dbname<\/strong>, el nombre de la base de datos que queremos examinar, acepta NULL para todas las bases de datos en la instancia actual y comodines<\/li>\n<li><strong>@schemaName<\/strong>, el nombre del esquema que queremos examinar, acepta NULL para todos los esquemas y comodines<\/li>\n<li><strong>@tableName<\/strong>, el nombre de la tabla que estamos buscando, acepta NULL para todas las tablas y comodines<\/li>\n<li><strong>@columnName<\/strong>, el nombre de la columna que estamos buscando, acepta NULL para todas las columnas y comodines. Establecer\u00e1 @onlyTablesList a 0<\/li>\n<li><strong>@onlyTablesList<\/strong>, Devolver\u00e1 solo la informaci\u00f3n de la tabla si 1, de lo contrario, todas las tablas y columnas que coinciden con los filtros<\/li>\n<\/ul>\n<p>&nbsp;<br \/>\nInformaci\u00f3n \u00fatil que proporciona a nivel de tabla:<\/p>\n<ul>\n<li><strong>tableType<\/strong>, para identificar tablas HEAP<\/li>\n<li><strong>row_count<\/strong>, para identificar tablas con muchas filas o sin filas en absoluto<\/li>\n<li><strong>TotalSpaceMB<\/strong>, para identificar tablas grandes en espacio en disco<\/li>\n<li><strong>LastUserAccess<\/strong>, para identificar tablas que no se usan<\/li>\n<li><strong>TotalUserAccess<\/strong>, para identificar tablas que son muy utilizadas<\/li>\n<li><strong>TableTriggers<\/strong>, para identificar tablas que tienen desencadenantes<\/li>\n<\/ul>\n<p>&nbsp;<br \/>\nInformaci\u00f3n \u00fatil que proporciona a nivel de columns:<\/p>\n<ul>\n<li><strong>DataType-Size<\/strong>, para identificar tipos de datos sobredimensionados, incorrectos o en desuso<\/li>\n<li><strong>Identity<\/strong>, para identificar columnas identity<\/li>\n<li><strong>Mandatory-DefaultValue<\/strong>, para identificar columnas NULL\/NOT NULL o con valores por defecto<\/li>\n<li><strong>PrimaryKey<\/strong>, para identificar las columnas de clave principal<\/li>\n<li><strong>Collation<\/strong>, para identificar columnas que pueden tener diferentes intercalaciones de la base de datos<\/li>\n<li><strong>ForeignKey-ReferencedColumn<\/strong>, para identificar las claves externas y la tabla.columna a la que hacen referencia<\/li>\n<\/ul>\n<p>&nbsp;<br \/>\nComo podeis ver, el procedimiento es muy vers\u00e1til y puede utilizarse por muchas razones, desde obtener una primera vistazo de una nueva base de datos hasta para obtener una visi\u00f3n completa de cada campo y la relaci\u00f3n entre las diferentes tablas si est\u00e1n definidas.<\/p>\n<p>&nbsp;<br \/>\n<strong>El procedimiento<\/strong><\/p>\n<p>Entonces, despu\u00e9s de haber visto para qu\u00e9 puede usarlo y c\u00f3mo, es hora de ver el c\u00f3digo. Pod\u00e9is agregarlo a vuestra propia base de datos de DBA o [master] si lo prefiere.<\/p>\n<p>&nbsp;<\/p>\n<pre class=\"brush: tsql; title: ; notranslate\" title=\"\">\r\nUSE [master]\r\nGO\r\nIF OBJECT_ID ('[dbo].[sqlg_tableDescription]') IS NULL EXECUTE sp_executesql N'CREATE PROCEDURE [dbo].[sqlg_tableDescription] AS SELECT 1 AS col1'\r\nGO\r\nSET ANSI_NULLS ON\r\nGO\r\nSET QUOTED_IDENTIFIER ON\r\nGO\r\n-- =============================================\r\n--\tCopyright 2018 Raul Gonzalez @SQLDoubleG\r\n-- \r\n-- Permission is hereby granted, free of charge, to any person obtaining a copy of this software \r\n-- and associated documentation files (the &amp;quot;Software&amp;quot;), to deal in the Software without restriction, \r\n-- including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, \r\n-- and\/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so,\r\n-- subject to the following conditions:\r\n--\r\n-- The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.\r\n--\r\n-- THE SOFTWARE IS PROVIDED &amp;quot;AS IS&amp;quot;, WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, \r\n-- INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. \r\n-- IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY,\r\n-- WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE \r\n-- OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.\r\n--\r\n--\r\n-- Author:\t\tRaul Gonzalez \r\n-- Create date: 17\/07\/2013 \r\n-- Description:\tReturns descriptive information for a database, schema or table specified. \r\n-- \r\n-- Assupmtions:\tDepending on the user executing the SP, the metadata displayed can vary \r\n--\t\t\t\tUser 'reporting' is granted with view definition permission to guarantee all info is complete in the report server \r\n-- \r\n-- Change Log:\t24\/09\/2013 RAG Added column row_count for tables \r\n--\t\t\t\t25\/10\/2013 RAG Changed the calculation of row_count and added TotalSpaceMB, UsedSpaceMB and UnusedSpaceMB for tables \r\n--\t\t\t\t28\/10\/2013 RAG Added filter to not process Objects created by an internal SQL Server component (is_ms_shipped = 0) \r\n--\t\t\t\t29\/10\/2013 RAG Added parameter column name, if provided, only tables which match the column name will appear on the list \r\n--\t\t\t\t29\/10\/2013 RAG @tableName and @columnName can come with wildcards as the comparison use LIKE  \r\n--\t\t\t\t29\/10\/2013 RAG included column Collation  \r\n--\t\t\t\t14\/11\/2013 RAG Changed the comparison of database name to like, this way we can call the SP with database name 'dbName%' \r\n--\t\t\t\t02\/12\/2013 RAG Added column IsIdentity \r\n--\t\t\t\t11\/12\/2013 RAG Added a new resultset for when @columnName is specified \r\n--\t\t\t\t13\/12\/2013 RAG Added a DataSpace column \r\n--\t\t\t\t28\/02\/2014 RAG Change on how data sizes are calculated to get same results as Table - properties - storage, also included Index Size \r\n--\t\t\t\t29\/10\/2014 RAG Added Columns LastUserAccess and TotalUserAccess aggregating data from sys.dm_db_index_usage_stats \r\n--\t\t\t\t09\/03\/2015 RAG Added Columns tableType, HEAP or CLUSTERED \r\n--\t\t\t\t30\/03\/2015 RAG Changed the way of counting rows to consider partitioned tables \r\n--\t\t\t\t01\/05\/2015 RAG Use total_pages to calculate total size \r\n--\t\t\t\t22\/03\/2016 RAG Added indexed views to the results \r\n--\t\t\t\t\t\t\t\tCreate temp table for columns using SELECT INTO as it is shorter and have the same definition \r\n--\t\t\t\t13\/04\/2016 RAG Changed the way [IndexSpaceUsed] is calculated to display total size of non clustered indexes \r\n--\t\t\t\t14\/07\/2016 SZO Removed no longer necessary comments from the code block. \r\n--\t\t\t\t04\/10\/2016 RAG Changes due to case sensitivity (column definition) \r\n--\t\t\t\t15\/02\/2017 RAG Changed table type to include Clustered columnstore by getting indexes.type_desc instead of hardcoded HEAP and CLUSTERED \r\n--\t\t\t\t \r\n-- ============================================= \r\nALTER PROCEDURE [dbo].[sqlg_tableDescription]\r\n\t@dbname\t\t\t\tSYSNAME = NULL \r\n\t, @schemaName\t\tSYSNAME = NULL \r\n\t, @tableName\t\tSYSNAME = NULL \r\n\t, @columnName\t\tSYSNAME = NULL \r\n\t, @onlyTablesList\tBIT\t\t= 0 \r\nAS \r\nBEGIN \r\n\t \r\n\tSET NOCOUNT ON \r\n\t \r\n\tIF @columnName IS NOT NULL BEGIN SET @onlyTablesList = 0 END \r\n\r\n\tDECLARE @StringSQLVersion\tNVARCHAR(128)\t= CONVERT(NVARCHAR(128),SERVERPROPERTY('ProductVersion'))\r\n\tDECLARE @NumericSQLVersion\tDECIMAL(3,1)\t= CONVERT(DECIMAL(3,1), (LEFT( @StringSQLVersion,  CHARINDEX('.', @StringSQLVersion, 0) + 1 )) )\r\n\r\n\tCREATE TABLE #resultTables \r\n\t\t( databaseName\t\tSYSNAME\t\t\tNULL \r\n\t\t, schemaName\t\tSYSNAME\t\t\tNULL \r\n\t\t, tableName\t\t\tSYSNAME\t\t\tNULL \r\n\t\t, tableType\t\t\tSYSNAME\t\t\tNULL \r\n\t\t, row_count\t\t\tINT\t\t\t\tNULL \r\n\t\t, TotalSpaceMB\t\tDECIMAL(15,3)\tNULL \r\n\t\t, DataSpaceMB\t\tDECIMAL(15,3)\tNULL \r\n\t\t, IndexSpaceMB\t\tDECIMAL(15,3)\tNULL \r\n\t\t, UnusedSpaceMB\t\tDECIMAL(15,3)\tNULL \r\n\t\t, TableTriggers\t\tVARCHAR(500)\tNULL \r\n\t\t, LastUserAccess\tDATETIME\t\tNULL \r\n\t\t, TotalUserAccess\tBIGINT\t\t\tNULL \r\n\t\t, LastUserLookup\tDATETIME\t\tNULL \r\n\t\t, Column_id\t\t\tINT\t\t\t\tNULL \r\n\t\t, columnName\t\tSYSNAME\t\t\tNULL \r\n\t\t, DataType\t\t\tSYSNAME\t\t\tNULL \r\n\t\t, Size\t\t\t\tVARCHAR(30)\t\tNULL \r\n\t\t, IsIdentity\t\tVARCHAR(3)\t\tNULL \r\n\t\t, Mandatory\t\t\tVARCHAR(3)\t\tNULL \r\n\t\t, DefaultValue\t\tNVARCHAR(MAX)\tNULL \r\n\t\t, PrimaryKey\t\tVARCHAR(3)\t\tNULL \r\n\t\t, ForeignKey\t\tVARCHAR(3)\t\tNULL \r\n\t\t, IsComputed\t\tVARCHAR(3)\t\tNULL \r\n\t\t, Collation\t\t\tSYSNAME\t\t\tNULL \r\n\t\t, [definition]\t\tNVARCHAR(MAX)\tNULL \r\n\t\t, Filestream\t\tVARCHAR(3)\t\tNULL \r\n\t\t, ReferencedColumn\tSYSNAME\t\t\tNULL \r\n\t\t, TableDescription\tSQL_VARIANT \r\n\t\t, ColDescription\tSQL_VARIANT) \r\n\t \r\n\tSELECT *  \r\n\t\tINTO #resultColumns \r\n\t\tFROM #resultTables \r\n\t\tWHERE 1=0 \r\n \r\n\tCREATE TABLE #db_triggers \r\n\t\t( parent_id\t\t\tINT \r\n\t\t, trigger_type\t\tNVARCHAR(4000)) \r\n\t \r\n\tCREATE TABLE #index_usage_stats \r\n\t\t( object_id\t\t\tINT \r\n\t\t, last_user_access\tDATETIME\t\tNULL \r\n\t\t, total_user_access\tBIGINT\t\t\tNULL) \r\n \r\n\tDECLARE @databases TABLE  \r\n\t\t( ID\t\t\t\tINT IDENTITY \r\n\t\t, dbname\t\t\tSYSNAME) \r\n\t\t \r\n\tDECLARE @sql\t\t\tNVARCHAR(MAX) \r\n\t\t\t, @countDBs\t\tINT = 1 \r\n\t\t\t, @numDBs\t\tINT \r\n \r\n\tINSERT INTO @databases  \r\n\t\tSELECT TOP 100 PERCENT name  \r\n\t\t\tFROM sys.databases  \r\n\t\t\tWHERE [name] NOT IN ('model', 'tempdb')  \r\n\t\t\t\tAND state = 0  \r\n\t\t\t\tAND name LIKE ISNULL(@dbname, name) \r\n\t\t\tORDER BY name ASC\t\t \r\n \r\n\tSET @numDBs = @@ROWCOUNT \r\n \r\n\tWHILE @countDBs &amp;lt;= @numDBs BEGIN \r\n\t\t \r\n\t\tSELECT @dbname = dbname  \r\n\t\t\tFROM @databases \r\n\t\t\tWHERE ID = @countDBs \r\n \r\n\t\tSET @sql = N' \r\n\t\t \r\n\t\t\tUSE ' + QUOTENAME(@dbname) + N' \r\n \r\n\t\t\tDECLARE @schemaName SYSNAME = ' + ISNULL('''' + @schemaName+ '''','NULL')  + N' \r\n\t\t\tDECLARE @tableName\tSYSNAME = ' + ISNULL('''' + @tableName + '''','NULL')  + CONVERT(NVARCHAR(MAX), N' \r\n \r\n\t\t\tTRUNCATE TABLE #db_triggers \r\n\t\t\tTRUNCATE TABLE #index_usage_stats \r\n \r\n\t\t\tINSERT INTO #db_triggers \r\n\t\t\tSELECT parent_id \r\n\t\t\t\t\t, name + '' ('' + \r\n\t\t\t\t\t\tCASE WHEN tr.is_instead_of_trigger = 1 THEN ''INSTEAD OF '' ELSE ''AFTER '' END + \r\n\t\t\t\t\t\tSTUFF( (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, '''') + \r\n\t\t\t\t\t\tCASE WHEN tr.is_disabled = 1 THEN '' (Disabled)'' ELSE '''' END + '')'' \r\n\t\t\t\t\t\tAS trigger_type\t\t\t\t \r\n\t\t\t\tFROM sys.triggers AS tr \r\n \r\n\t\t\t;WITH t AS( \r\n\t\t\t\tSELECT object_id, [last_user_lookup] AS last_user_access , ( ius.user_scans + ius.user_seeks + ius.user_lookups ) AS total_user_access \r\n\t\t\t\t\tFROM sys.dm_db_index_usage_stats AS ius WHERE database_id = DB_ID() \r\n\t\t\t\tUNION ALL\t \r\n\t\t\t\tSELECT object_id, [last_user_scan] , 0 -- Zero because we just have to count user access once, not for each date!! \r\n\t\t\t\t\tFROM sys.dm_db_index_usage_stats AS ius WHERE database_id = DB_ID() \r\n\t\t\t\tUNION ALL\t \r\n\t\t\t\tSELECT object_id, [last_user_seek] , 0 -- Zero because we just have to count user access once, not for each date!! \r\n\t\t\t\t\tFROM sys.dm_db_index_usage_stats AS ius WHERE database_id = DB_ID() \r\n\t\t\t)  \r\n\t\t\tINSERT INTO #index_usage_stats \r\n\t\t\t\tSELECT object_id, MAX(last_user_access), SUM(CONVERT(BIGINT,total_user_access))  FROM t \r\n\t\t\t\t\tGROUP BY object_id \r\n\t\t\t\t\t\t\t \r\n\t\t\tINSERT INTO #resultTables \r\n\t\t\t\t\t(databaseName \r\n\t\t\t\t\t, schemaName \r\n\t\t\t\t\t, tableName \r\n\t\t\t\t\t, tableType \r\n\t\t\t\t\t, row_count \r\n\t\t\t\t\t, TotalSpaceMB \r\n\t\t\t\t\t, DataSpaceMB \r\n\t\t\t\t\t, IndexSpaceMB \r\n\t\t\t\t\t, UnusedSpaceMB \r\n\t\t\t\t\t, Column_id \r\n\t\t\t\t\t, TableDescription \r\n\t\t\t\t\t, TableTriggers \r\n\t\t\t\t\t, LastUserAccess \r\n\t\t\t\t\t, TotalUserAccess) \r\n\t\t\tSELECT DB_NAME() \r\n\t\t\t\t\t, OBJECT_SCHEMA_NAME(i.object_id)  \r\n\t\t\t\t\t, OBJECT_NAME(i.object_id)  \r\n\t\t\t\t\t, CASE  \r\n\t\t\t\t\t\t\tWHEN OBJECTPROPERTYEX(i.object_id, ''IsView'') = 1 THEN ''INDEXED VIEW''  \r\n\t\t\t\t\t\t\tELSE i.type_desc \r\n\t\t\t\t\t\tEND AS tableType  \r\n \r\n\t\t\t\t\t \r\n\t\t\t\t\t, SUM(CASE WHEN a.type = 1 THEN p.rows ELSE 0 END)  \r\n\t\t\t\t\t, CONVERT( DECIMAL(15,3), ISNULL( (8.000000 * SUM(a.total_pages)) \/ 1024 , 0 ) ) AS [TotalSpaceUsed] \r\n\t\t\t\t\t, CONVERT( DECIMAL(15,3), ISNULL( (8.000000 * SUM(               CASE WHEN a.type &amp;lt;&amp;gt; 1 THEN a.used_pages WHEN p.index_id &amp;lt; 2 THEN a.data_pages ELSE 0 END)) \/ 1024 , 0 ) ) AS [DataSpaceUsed] \r\n\t\t\t\t\t \r\n\t\t\t\t\t-- wrong calculation, use OUTER APPLY \r\n\t\t\t\t\t--, CONVERT( DECIMAL(15,3), ISNULL( (8.000000 * SUM(a.used_pages - CASE WHEN a.type &amp;lt;&amp;gt; 1 THEN a.used_pages WHEN p.index_id &amp;lt; 2 THEN a.data_pages ELSE 0 END)) \/ 1024 , 0 ) ) AS [IndexSpaceUsed] \r\n\t\t\t\t\t, CONVERT( DECIMAL(15,3), ixsp.[IndexSpaceUsed] \/ 1024. ) AS [IndexSpaceUsed] \r\n \r\n \r\n\t\t\t\t\t--, CONVERT( DECIMAL(9,2), (SUM(a.total_pages) * 8) \/ 1024. ) AS TotalSpaceMB \r\n\t\t\t\t\t--, CONVERT( DECIMAL(9,2), (SUM(a.data_pages) * 8) \/ 1024. ) AS DataSpaceMB \r\n\t\t\t\t\t--, CONVERT( DECIMAL(9,2), (SUM(a.used_pages)  * 8) \/ 1024. ) AS UsedSpaceMB \r\n\t\t\t\t\t, CONVERT( DECIMAL(9,2), ((SUM(a.total_pages) - SUM(a.used_pages)) * 8 \/ 1024.) )  AS UnusedSpaceMB \r\n \r\n\t\t\t\t\t, 0 \r\n\t\t\t\t\t, xp.value \r\n\t\t\t\t\t, STUFF( (SELECT '', '' + trigger_type FROM #db_triggers AS tr WHERE tr.parent_id = i.object_id FOR XML PATH('''')  ),1, 2, '''')  AS triggers \r\n\t\t\t\t\t, ius.last_user_access \r\n\t\t\t\t\t, total_user_access \r\n\t\t\t\tFROM sys.indexes AS i  \r\n\t\t\t\t\tINNER JOIN sys.partitions AS p \r\n\t\t\t\t\t\tON p.object_id = i.object_id \r\n\t\t\t\t\t\t\tAND p.index_id = i.index_id \r\n\t\t\t\t\tINNER JOIN sys.allocation_units AS a  \r\n\t\t\t\t\t\tON a.container_id = p.partition_id  \r\n\t\t\t\t\tLEFT JOIN sys.extended_properties AS xp \r\n\t\t\t\t\t\tON xp.major_id = i.object_id \r\n\t\t\t\t\t\t\tAND xp.minor_id = 0 \r\n\t\t\t\t\t\t\tAND xp.name = ''MS_Description'' \r\n \r\n\t\t\t\t\tLEFT JOIN #index_usage_stats AS ius \r\n\t\t\t\t\t\tON ius.object_id = i.object_id  \r\n\t\t\t\t\tOUTER APPLY (\r\n\t\t\t\t\t\tSELECT  \r\n\t\t\t\t\t\t\t\t') +  CASE WHEN @NumericSQLVersion &amp;gt; 11.0 THEN CONVERT(NVARCHAR(MAX), ' \r\n\t\t\t\t\t\t\t\tCASE WHEN (tbl.is_memory_optimized=0) THEN ') ELSE CONVERT(NVARCHAR(MAX),'') END  \r\n\t\t\t\t\t\t\t+ CONVERT(NVARCHAR(MAX), ' \r\n\t\t\t\t\t\t\t\t\tISNULL(( \r\n\t\t\t\t\t\t\t\t\t(SELECT SUM (used_page_count) FROM sys.dm_db_partition_stats ps WHERE ps.object_id = tbl.object_id) \r\n\t\t\t\t\t\t\t\t\t+ ( 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)) \r\n\t\t\t\t\t\t\t\t\t\tWHEN 0 THEN 0 \r\n\t\t\t\t\t\t\t\t\t\tELSE ( \r\n\t\t\t\t\t\t\t\t\t\t  SELECT sum(p.used_page_count) \r\n\t\t\t\t\t\t\t\t\t\t  FROM sys.dm_db_partition_stats p, sys.internal_tables it \r\n\t\t\t\t\t\t\t\t\t\t  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) \r\n\t\t\t\t\t\t\t\t\t\tEND ) \r\n\t\t\t\t\t\t\t\t\t- (SELECT SUM (CASE WHEN(index_id &amp;lt; 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count) ELSE 0 END) \r\n\t\t\t\t\t\t\t\t\t   FROM sys.dm_db_partition_stats WHERE object_id = tbl.object_id) \r\n\t\t\t\t\t\t\t\t\t) * 8, 0.0) \r\n\t\t\t\t\t\t\t') +  CASE WHEN @NumericSQLVersion &amp;gt; 11.0 THEN CONVERT(NVARCHAR(MAX),' \r\n\t\t\t\t\t\t\t\telse \r\n\t\t\t\t\t\t\t\t\tisnull((select (tms.[memory_used_by_indexes_kb]) \r\n\t\t\t\t\t\t\t\t\tfrom [sys].[dm_db_xtp_table_memory_stats] tms \r\n\t\t\t\t\t\t\t\t\twhere tms.object_id = tbl.object_id), 0.0) \r\n\t\t\t\t\t\t\t\tend') ELSE CONVERT(NVARCHAR(MAX),'') END  \r\n\t\t\t\t\t\t\t+ CONVERT(NVARCHAR(MAX), ' \r\n\t\t\t\t\t\t\t   AS [IndexSpaceUsed]\r\n\t\t\t\t\t\tFROM sys.tables AS tbl \r\n\t\t\t\t\t\tWHERE tbl.object_id = i.object_id \r\n\t\t\t\t\t) AS ixsp  \r\n\t\t\t\tWHERE OBJECTPROPERTYEX(i.object_id, ''IsMSShipped'') = 0 \r\n\t\t\t\t\tAND i.index_id IN (0, 1) -- Heap or Clustered \r\n\t\t\t\t\tAND OBJECT_SCHEMA_NAME(i.object_id) = ISNULL(@schemaName, OBJECT_SCHEMA_NAME(i.object_id)) \r\n\t\t\t\t\tAND OBJECT_NAME(i.object_id) LIKE ISNULL(@tableName, OBJECT_NAME(i.object_id)) \r\n\t\t\t\tGROUP BY i.object_id \r\n\t\t\t\t\t\t--, t.name \r\n\t\t\t\t\t\t, i.index_id \r\n\t\t\t\t\t\t, i.type_desc \r\n\t\t\t\t\t\t, ixsp.[IndexSpaceUsed] \r\n\t\t\t\t\t\t--, p.rows \r\n\t\t\t\t\t\t, xp.value \r\n\t\t\t\t\t\t, ius.last_user_access \r\n\t\t\t\t\t\t, total_user_access \r\n\t\t') \r\n\t\tPRINT @sql \r\n\t\t-- Insert all tables with their descriptions \r\n\t\tEXECUTE sp_executesql @sql \r\n\t \r\n\t\tIF @onlyTablesList = 0 BEGIN  \r\n \r\n\t\t\tSET @sql = N' \r\n\t\t\t \r\n\t\t\t\tUSE ' + QUOTENAME(@dbname) + N' \r\n \r\n\t\t\t\tDECLARE @schemaName SYSNAME = ' + ISNULL('''' + @schemaName+ '''','NULL')  + N' \r\n\t\t\t\tDECLARE @tableName\tSYSNAME = ' + ISNULL('''' + @tableName + '''','NULL')  + N' \r\n\t\t\t\tDECLARE @columnName\tSYSNAME = ' + ISNULL('''' + @columnName + '''','NULL')  + N' \r\n\t\t\t \r\n\t\t\t\tINSERT INTO #resultColumns \r\n\t\t\t\t\t\t(databaseName \r\n\t\t\t\t\t\t, schemaName \r\n\t\t\t\t\t\t, tableName \r\n\t\t\t\t\t\t, Column_id \r\n\t\t\t\t\t\t, columnName \r\n\t\t\t\t\t\t, DataType \r\n\t\t\t\t\t\t, Size \r\n\t\t\t\t\t\t, IsIdentity \r\n\t\t\t\t\t\t, Mandatory \r\n\t\t\t\t\t\t, DefaultValue \r\n\t\t\t\t\t\t, PrimaryKey \r\n\t\t\t\t\t\t, ForeignKey \r\n\t\t\t\t\t\t, IsComputed \r\n\t\t\t\t\t\t, Collation \r\n\t\t\t\t\t\t, [definition] \r\n\t\t\t\t\t\t, Filestream \r\n\t\t\t\t\t\t, ReferencedColumn \r\n\t\t\t\t\t\t, TableDescription \r\n\t\t\t\t\t\t, ColDescription) \r\n\t\t\t\tSELECT DB_NAME() \r\n\t\t\t\t\t\t, OBJECT_SCHEMA_NAME(i.object_id) \r\n\t\t\t\t\t\t, OBJECT_NAME(i.object_id) \r\n\t\t\t\t\t\t, c.column_id \r\n\t\t\t\t\t\t, c.name \r\n\t\t\t\t\t\t, UPPER(ty.name) \r\n\t\t\t\t\t\t, CASE  \r\n\t\t\t\t\t\t\t\tWHEN ty.user_type_id IN (165,167,231) AND c.max_length = -1 THEN ''Unlimited'' \r\n\t\t\t\t\t\t\t\tWHEN ty.user_type_id IN (231,239) THEN CONVERT(VARCHAR,c.max_length\/2) \r\n\t\t\t\t\t\t\t\tWHEN ty.user_type_id IN (165,167,173,175,231,239) THEN CONVERT(VARCHAR,c.max_length) \r\n\t\t\t\t\t\t\t\tELSE '''' \r\n\t\t\t\t\t\t\tEND \r\n\t\t\t\t\t\t, CASE WHEN c.is_identity = 1 THEN ''Yes''  \r\n\t\t\t\t\t\t\t\tELSE '''' \r\n\t\t\t\t\t\t\tEND \r\n\t\t\t\t\t\t, CASE  \r\n\t\t\t\t\t\t\t\tWHEN c.is_nullable = 1 THEN ''No''  \r\n\t\t\t\t\t\t\t\tELSE ''Yes'' \r\n\t\t\t\t\t\t\tEND \r\n\t\t\t\t\t\t, CASE  \r\n\t\t\t\t\t\t\t\tWHEN df.definition IS NOT NULL THEN SUBSTRING(df.definition, 2, LEN(df.definition)-2) -- to remove extra parenthesis \r\n\t\t\t\t\t\t\t\tELSE '''' \r\n\t\t\t\t\t\t\tEND \r\n\t\t\t\t\t\t, CASE WHEN EXISTS (SELECT 1  \r\n\t\t\t\t\t\t\t\t\t\t\t\tFROM sys.index_columns AS ixc  \r\n\t\t\t\t\t\t\t\t\t\t\t\t\tLEFT JOIN sys.indexes AS ix \r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tON ix.object_id = i.object_id \r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND ix.index_id = ixc.index_id \r\n\t\t\t\t\t\t\t\t\t\t\t\tWHERE ixc.object_id = i.object_id  \r\n\t\t\t\t\t\t\t\t\t\t\t\t\tAND ixc.column_id = c.column_id  \r\n\t\t\t\t\t\t\t\t\t\t\t\t\tAND ix.is_primary_key = 1 ) THEN ''Yes'' \r\n\t\t\t\t\t\t\t\tELSE '''' \r\n\t\t\t\t\t\t\tEND \r\n\t\t\t\t\t\t, CASE  \r\n\t\t\t\t\t\t\t\tWHEN fk.object_id IS NOT NULL THEN ''Yes'' \r\n\t\t\t\t\t\t\t\tELSE '''' \r\n\t\t\t\t\t\t\tEND \r\n\t\t\t\t\t\t, CASE  \r\n\t\t\t\t\t\t\tWHEN c.is_computed = 1 THEN ''Yes''  \r\n\t\t\t\t\t\t\tELSE '''' \r\n\t\t\t\t\t\tEND \r\n\t\t\t\t\t\t, c.Collation_name \r\n\t\t\t\t\t\t, cc.definition \r\n\t\t\t\t\t\t, CASE  \r\n\t\t\t\t\t\t\tWHEN c.is_filestream = 1 THEN ''Yes''  \r\n\t\t\t\t\t\t\tELSE '''' \r\n\t\t\t\t\t\tEND \r\n\t\t\t\t\t\t, ISNULL(OBJECT_SCHEMA_NAME(rc.object_id) + ''.'' + OBJECT_NAME(rc.object_id) + ''.'' + rc.name, '''') \r\n\t\t\t\t\t\t, NULL \r\n\t\t\t\t\t\t, xp.value \r\n\t\t\t\t\tFROM sys.indexes AS i \r\n\t\t\t\t\t\tINNER JOIN sys.columns AS c \r\n\t\t\t\t\t\t\tON c.object_id = i.object_id \r\n\t\t\t\t\t\t\t\tAND i.index_id IN (0,1) \r\n\t\t\t\t\t\tLEFT JOIN sys.types AS ty \r\n\t\t\t\t\t\t\tON ty.user_type_id = c.user_type_id \r\n\t\t\t\t\t\tLEFT JOIN sys.default_constraints AS df \r\n\t\t\t\t\t\t\tON df.parent_object_id = c.object_id \r\n\t\t\t\t\t\t\t\tAND df.parent_column_id = c.column_id \r\n\t\t\t\t\t\tLEFT JOIN sys.computed_columns AS cc \r\n\t\t\t\t\t\t\tON cc.object_id = i.object_id \r\n\t\t\t\t\t\t\t\tAND cc.column_id = c.column_id \r\n\t\t\t\t\t\tLEFT JOIN sys.foreign_key_columns AS fkc \r\n\t\t\t\t\t\t\tON fkc.parent_object_id = c.object_id \r\n\t\t\t\t\t\t\t\tAND fkc.parent_column_id = c.column_id \r\n\t\t\t\t\t\tLEFT JOIN sys.foreign_keys AS fk \r\n\t\t\t\t\t\t\tON fk.object_id = fkc.constraint_object_id \r\n\t\t\t\t\t\tLEFT JOIN sys.columns AS rc \r\n\t\t\t\t\t\t\tON rc.object_id = fkc.referenced_object_id \r\n\t\t\t\t\t\t\t\tAND rc.column_id = fkc.referenced_column_id \r\n\t\t\t\t\t\tLEFT JOIN sys.extended_properties AS xp \r\n\t\t\t\t\t\t\tON xp.major_id = i.object_id \r\n\t\t\t\t\t\t\t\tAND xp.minor_id = c.column_id  \r\n\t\t\t\t\t\t\t\tAND xp.name = ''MS_Description'' \r\n\t\t\t\t\tWHERE OBJECTPROPERTYEX(i.object_id, ''IsMSShipped'') = 0 \r\n\t\t\t\t\t\tAND OBJECT_SCHEMA_NAME(i.object_id) = ISNULL(@schemaName, OBJECT_SCHEMA_NAME(i.object_id)) \r\n\t\t\t\t\t\tAND OBJECT_NAME(i.object_id) LIKE ISNULL(@tableName, OBJECT_NAME(i.object_id)) \r\n\t\t\t\t\t\tAND c.name LIKE ISNULL(@columnName, c.name) \r\n\t\t\t' \r\n\t\t\t--PRINT @sql \r\n\t\t\t-- Insert all Columns for all tables\t\t\t \r\n\t\t\tEXECUTE sp_executesql @sql \r\n \r\n\t\tEND -- IF @onlyTablesList = 0  \r\n \r\n\t\tSET @countDBs += 1\t \r\n\tEND \r\n \r\n \r\n\tIF @onlyTablesList = 1 BEGIN \r\n\t\tSELECT ISNULL(databaseName,'') AS databaseName \r\n\t\t\t\t, ISNULL(schemaName,'') AS schemaName \r\n\t\t\t\t, ISNULL(tableName,'') AS tableName \r\n\t\t\t\t, ISNULL(tableType,'') AS tableType \r\n\t\t\t\t, ISNULL(CONVERT(VARCHAR,row_count), '') AS row_count \r\n\t\t\t\t, ISNULL(CONVERT(VARCHAR,TotalSpaceMB), '') AS TotalSpaceMB \r\n\t\t\t\t, ISNULL(CONVERT(VARCHAR,DataSpaceMB), '') AS DataSpaceMB \r\n\t\t\t\t, ISNULL(CONVERT(VARCHAR,IndexSpaceMB), '') AS IndexSpaceMB \r\n\t\t\t\t, ISNULL(CONVERT(VARCHAR,UnusedSpaceMB), '') AS UnusedSpaceMB \r\n\t\t\t\t, ISNULL(CONVERT(VARCHAR,LastUserAccess, 113), '') AS LastUserAccess \r\n\t\t\t\t, ISNULL(CONVERT(VARCHAR,TotalUserAccess), '') AS TotalUserAccess \r\n\t\t\t\t, ISNULL(TableTriggers,'') AS TableTriggers \r\n\t\t\t\t, ISNULL(TableDescription,'') AS TableDescription \r\n\t\t\tFROM #resultTables \r\n\t\t\tORDER BY databaseName \r\n\t\t\t\t, schemaName \r\n\t\t\t\t, tableName \r\n\tEND  \r\n\tELSE BEGIN \r\n \r\n\t-- Delete tables where the columName provided was not found \r\n\t\tDELETE rt \r\n\t\t\tFROM #resultTables AS rt \r\n\t\t\t\tLEFT JOIN #resultColumns AS rc \r\n\t\t\t\t\tON rc.databaseName = rt.databaseName \r\n\t\t\t\t\t\tAND rc.tableName = rt.tableName \r\n\t\t\tWHERE rc.databaseName IS NULL \r\n\t\t\t\tAND ISNULL(@columnName, '') &amp;lt;&amp;gt; '' \r\n \r\n\t\t--IF @columnName IS NULL BEGIN \r\n\t\t\t;WITH cte AS( \r\n\t\t\t\tSELECT databaseName \r\n\t\t\t\t\t\t, schemaName \r\n\t\t\t\t\t\t, tableName \r\n\t\t\t\t\t\t, tableType \r\n\t\t\t\t\t\t, row_count \r\n\t\t\t\t\t\t, TotalSpaceMB \r\n\t\t\t\t\t\t, DataSpaceMB \r\n\t\t\t\t\t\t, IndexSpaceMB \r\n\t\t\t\t\t\t, UnusedSpaceMB \r\n\t\t\t\t\t\t, LastUserAccess \r\n\t\t\t\t\t\t, TotalUserAccess \r\n\t\t\t\t\t\t, TableTriggers \r\n\t\t\t\t\t\t, columnName \r\n\t\t\t\t\t\t, Column_id \r\n\t\t\t\t\t\t, DataType \r\n\t\t\t\t\t\t, Size \r\n\t\t\t\t\t\t, IsIdentity \r\n\t\t\t\t\t\t, Mandatory \r\n\t\t\t\t\t\t, DefaultValue \r\n\t\t\t\t\t\t, PrimaryKey \r\n\t\t\t\t\t\t, IsComputed \r\n\t\t\t\t\t\t, Collation \r\n\t\t\t\t\t\t, [definition] \r\n\t\t\t\t\t\t, ForeignKey \r\n\t\t\t\t\t\t, ReferencedColumn \r\n\t\t\t\t\t\t, [Filestream]\t\t\t \r\n\t\t\t\t\t\t, TableDescription \r\n\t\t\t\t\t\t, ColDescription \r\n\t\t\t\t\tFROM #resultTables \r\n\t\t\t\tUNION ALL \r\n\t\t\t\tSELECT databaseName \r\n\t\t\t\t\t\t, schemaName \r\n\t\t\t\t\t\t, tableName \r\n\t\t\t\t\t\t, tableType \r\n\t\t\t\t\t\t, row_count \r\n\t\t\t\t\t\t, TotalSpaceMB \r\n\t\t\t\t\t\t, DataSpaceMB \r\n\t\t\t\t\t\t, IndexSpaceMB \r\n\t\t\t\t\t\t, UnusedSpaceMB \r\n\t\t\t\t\t\t, LastUserAccess \r\n\t\t\t\t\t\t, TotalUserAccess \r\n\t\t\t\t\t\t, TableTriggers \r\n\t\t\t\t\t\t, columnName \r\n\t\t\t\t\t\t, Column_id \r\n\t\t\t\t\t\t, DataType \r\n\t\t\t\t\t\t, Size \r\n\t\t\t\t\t\t, IsIdentity \r\n\t\t\t\t\t\t, Mandatory \r\n\t\t\t\t\t\t, DefaultValue \r\n\t\t\t\t\t\t, PrimaryKey \r\n\t\t\t\t\t\t, IsComputed \r\n\t\t\t\t\t\t, Collation \r\n\t\t\t\t\t\t, [definition] \r\n\t\t\t\t\t\t, ForeignKey \r\n\t\t\t\t\t\t, ReferencedColumn \r\n\t\t\t\t\t\t, [Filestream] \r\n\t\t\t\t\t\t, TableDescription \r\n\t\t\t\t\t\t, ColDescription \r\n\t\t\t\t\tFROM #resultColumns \r\n\t\t\t) \r\n\t\t\tSELECT ISNULL(databaseName,'') AS databaseName \r\n\t\t\t\t\t, ISNULL(schemaName,'') AS schemaName \r\n\t\t\t\t\t, ISNULL(tableName,'') AS tableName \r\n\t\t\t\t\t, ISNULL(tableType,'') AS tableType \r\n\t\t\t\t\t, ISNULL(CONVERT(VARCHAR,row_count),'') AS row_count \r\n\t\t\t\t\t, ISNULL(CONVERT(VARCHAR,TotalSpaceMB),'') AS TotalSpaceMB \r\n\t\t\t\t\t, ISNULL(CONVERT(VARCHAR,DataSpaceMB),'') AS DataSpaceMB \r\n\t\t\t\t\t, ISNULL(CONVERT(VARCHAR,IndexSpaceMB),'') AS IndexSpaceMB \r\n\t\t\t\t\t, ISNULL(CONVERT(VARCHAR,UnusedSpaceMB),'') AS UnusedSpaceMB \r\n\t\t\t\t\t, ISNULL(CONVERT(VARCHAR,LastUserAccess, 113), '') AS LastUserAccess \r\n\t\t\t\t\t, ISNULL(CONVERT(VARCHAR,TotalUserAccess), '') AS TotalUserAccess \r\n\t\t\t\t\t, ISNULL(TableTriggers,'') AS TableTriggers \r\n\t\t\t\t\t, ISNULL(columnName,'') AS columnName \r\n\t\t\t\t\t, ISNULL(DataType,'') AS DataType \r\n\t\t\t\t\t, ISNULL(Size,'') AS Size \r\n\t\t\t\t\t, ISNULL(IsIdentity,'') AS IsIdentity \r\n\t\t\t\t\t, ISNULL(Mandatory,'') AS Mandatory \r\n\t\t\t\t\t, ISNULL(DefaultValue,'') AS DefaultValue \r\n\t\t\t\t\t, ISNULL(PrimaryKey,'') AS PrimaryKey \r\n\t\t\t\t\t, ISNULL(IsComputed,'') AS IsComputed \r\n\t\t\t\t\t, ISNULL(Collation,'') AS Collation \r\n\t\t\t\t\t, ISNULL([definition],'') AS [Definition] \r\n\t\t\t\t\t, ISNULL(ForeignKey,'') AS ForeignKey \r\n\t\t\t\t\t, ISNULL(ReferencedColumn,'') AS ReferencedColumn \r\n\t\t\t\t\t, ISNULL(Filestream,'') AS Filestream\t\t\t \r\n\t\t\t\t\t, ISNULL(TableDescription,'') AS TableDescription \r\n\t\t\t\t\t, ISNULL(ColDescription,'') AS ColDescription \r\n\t\t\t\tFROM cte \r\n\t\t\t\tORDER BY databaseName \r\n\t\t\t\t\t, schemaName \r\n\t\t\t\t\t, tableName \r\n\t\t\t\t\t, Column_id \r\n\t \r\n\tEND \r\n\tDROP TABLE #resultTables \r\n\tDROP TABLE #resultColumns \r\n\tDROP TABLE #db_triggers \r\n\tDROP TABLE #index_usage_stats \r\n\t \r\nEND \r\nGO\r\n\r\n<\/pre>\n<p>&nbsp;<br \/>\n<strong>Conclusi\u00f3n<\/strong><\/p>\n<p>Espero que hayais encontrado \u00fatil este script y os recomiendo que os mantengais atentos porque pronto voy a anunciar algo importante.<\/p>\n<p>Quiero terminar esta publicaci\u00f3n agradeciendo a Bert por organizar el evento de este mes y elegir un tema tan bueno.<\/p>\n<p>Creo que la comunidad de SQL Server es uno de los puntos m\u00e1s 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.<\/p>\n<p>\u00a1Gracias!<br \/>\n&nbsp;<\/p>","protected":false},"excerpt":{"rendered":"<p>En este post quiero compartir un script que utilizo bastante a menudo por su versatilidad&nbsp; &nbsp;Esta publicaci\u00f3n forma parte de T-SQL Tuesday, que es una fiesta de blog mensual el segundo martes&#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4,19,43],"tags":[40,36,15,60],"_links":{"self":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts\/550"}],"collection":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/comments?post=550"}],"version-history":[{"count":1,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts\/550\/revisions"}],"predecessor-version":[{"id":620,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts\/550\/revisions\/620"}],"wp:attachment":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/media?parent=550"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/categories?post=550"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/tags?post=550"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}