¿Cómo enumero todas las tablas en todas las bases de datos en SQL Server en un solo conjunto de resultados?

Estoy buscando un código T-SQL para listar todas las tablas en todas las bases de datos en SQL Server (al menos en SS2005 y SS2008; sería bueno aplicar también a SS2000). La trampa, sin embargo, es que me gustaría un solo conjunto de resultados . Esto excluye la excelente respuesta de Pinal Dave :

sp_msforeachdb 'select "?" AS db, * from [?].sys.tables' 

El proceso almacenado anterior genera un conjunto de resultados por base de datos , lo cual está bien si está en un IDE como SSMS que puede mostrar múltiples conjuntos de resultados. Sin embargo, quiero un solo conjunto de resultados porque quiero una consulta que sea esencialmente una herramienta de “búsqueda”: si agrego una cláusula como WHERE tablename like '%accounts' entonces me dirá dónde encontrar mis Cuentas de cuentas, Cuentas de clientes y Cuentas de proveedores tablas independientemente de en qué base de datos residen


2010.05.20 Actualización, unos 20 minutos después …

Hasta el momento, la respuesta de Remus parece muy interesante. En lugar de publicar esto como una respuesta y adjudicarlo a mí mismo, estoy publicando una versión aquí que he modificado para incluir el nombre de la base de datos y una cláusula de filtro de muestra. Sin embargo, parece que Remus obtendrá el crédito por la respuesta en este momento.

 declare @sql nvarchar(max); set @sql = N'select b.name as "DB", a.name collate Latin1_General_CI_AI as "Table", object_id, schema_id, cast(1 as int) as database_id from master.sys.tables a join sys.databases b on database_id=1 where a.name like ''account%'''; select @sql = @sql + N' union all select b.name as "DB", a.name collate Latin1_General_CI_AI, object_id, schema_id, ' + cast(database_id as nvarchar(10)) + N' from ' + quotename(name) + N'.sys.tables a join sys.databases b on database_id=' + cast(database_id as nvarchar(10)) + 'where a.name like ''account%''' from sys.databases where database_id > 1 and state = 0 and user_access = 0; exec sp_executesql @sql; 

2010.05.24 Actualización – ¡Nuevo corredor de entrada!

Los comentarios y respuestas han sido geniales. La continua participación colaborativa ha llevado a un nuevo candidato : ¡la respuesta de KM del 21 de mayo!

Aquí están los problemas que descubrí con la solución de Remus:

Problema principal: los usuarios tienen diferentes permisos que hacen que la consulta tenga éxito en función de los datos (es decir, el valor de filtrado). Ejecutar en mi base de datos de producción sin filtrado (es decir, omitir la cláusula WHERE ) Recibí este error en varios DB a los que no tengo permiso de acceso:

El servidor principal “msorens” no puede acceder a la base de datos “ETLprocDB” en el contexto de seguridad actual.

La consulta tendrá éxito con algunas cláusulas de filtrado, aquellas que no tocan las bases de datos fuera de mi nivel de acceso.

Problema menor: No es fácilmente degradable para el soporte de SQL Server 2000 (sí, todavía hay algunos de nosotros que lo usan …) porque construye una sola cadena mientras acumula entradas para cada base de datos. Con mi sistema, superé la marca de 8000 caracteres en alrededor de 40 bases de datos.

Problema menor: Código duplicado: la configuración del bucle esencialmente duplica el cuerpo del bucle. Entiendo el razonamiento, pero es solo un motivo favorito mío …

La respuesta de KM no está afligida por estos problemas. El sp_msforeachdb almacenado sp_msforeachdb tiene en cuenta los permisos del usuario, por lo que evita los problemas de permisos. Todavía no he probado el código con SS2000, pero KM indica los ajustes que deberían hacerlo.

Estoy publicando a continuación mis modificaciones a la respuesta de KM en función de mis preferencias personales. Específicamente:

  • He eliminado el nombre del servidor, ya que realmente no agrega nada en el conjunto de resultados.
  • He dividido los componentes del nombre en sus propios campos en el conjunto de resultados (nombre del db, nombre del esquema y nombre de la tabla).
  • He introducido filtros separados para cada uno de los tres campos.
  • He agregado la clasificación por los tres campos (que se puede modificar según sus preferencias).

Aquí está mi modificación al código de KM (con un filtro de muestra aplicado solo al nombre de la tabla):

 SET NOCOUNT ON DECLARE @AllTables table (DbName sysname,SchemaName sysname, TableName sysname) DECLARE @SearchDb nvarchar(200) ,@SearchSchema nvarchar(200) ,@SearchTable nvarchar(200) ,@SQL nvarchar(4000) SET @SearchDb='%' SET @SearchSchema='%' SET @SearchTable='%Account%' SET @SQL='select ''?'' as DbName, s.name as SchemaName, t.name as TableName from [?].sys.tables t inner join sys.schemas s on t.schema_id=s.schema_id WHERE ''?'' LIKE '''+@SearchDb+''' AND s.name LIKE '''+@SearchSchema+''' AND t.name LIKE '''+@SearchTable+'''' INSERT INTO @AllTables (DbName, SchemaName, TableName) EXEC sp_msforeachdb @SQL SET NOCOUNT OFF SELECT * FROM @AllTables ORDER BY DbName, SchemaName, TableName 

para obtener una forma simple de obtener todas las tablas en el servidor, intente esto:

 SET NOCOUNT ON DECLARE @AllTables table (CompleteTableName nvarchar(4000)) INSERT INTO @AllTables (CompleteTableName) EXEC sp_msforeachdb 'select @@SERVERNAME+''.''+''?''+''.''+s.name+''.''+t.name from [?].sys.tables t inner join sys.schemas s on t.schema_id=s.schema_id' SET NOCOUNT OFF SELECT * FROM @AllTables ORDER BY 1 

devolverá una sola columna que contiene el servidor + base de datos + esquema + nombre de tabla: resultado de muestra:

 CompleteTableName -------------------------------------------- YourServer.YourDatabase1.YourSchema1.YourTable1 YourServer.YourDatabase1.YourSchema1.YourTable2 YourServer.YourDatabase1.YourSchema2.YourTable1 YourServer.YourDatabase1.YourSchema2.YourTable2 YourServer.YourDatabase2.YourSchema1.YourTable1 

si no está en SQL Server 2005 o superior, reemplace la DECLARE @AllTables table con CREATE TABLE #AllTables y luego cada @AllTables con #AllTables y funcionará.

EDITAR
aquí hay una versión que permitirá usar un parámetro de búsqueda en cualquier parte o partes de los nombres de servidor + base de datos + esquema + tabla:

 SET NOCOUNT ON DECLARE @AllTables table (CompleteTableName nvarchar(4000)) DECLARE @Search nvarchar(4000) ,@SQL nvarchar(4000) SET @Search=null --all rows SET @SQL='select @@SERVERNAME+''.''+''?''+''.''+s.name+''.''+t.name from [?].sys.tables t inner join sys.schemas s on t.schema_id=s.schema_id WHERE @@SERVERNAME+''.''+''?''+''.''+s.name+''.''+t.name LIKE ''%'+ISNULL(@SEARCH,'')+'%''' INSERT INTO @AllTables (CompleteTableName) EXEC sp_msforeachdb @SQL SET NOCOUNT OFF SELECT * FROM @AllTables ORDER BY 1 

Establezca @Search en NULL para todas las tablas, configúrelo en elementos como ‘dbo.users’ o ‘users’ o ‘.master.dbo’ o incluso incluya comodines como ‘.master.%. u’, etc.

 declare @sql nvarchar(max); set @sql = N'select cast(''master'' as sysname) as db_name, name collate Latin1_General_CI_AI, object_id, schema_id, cast(1 as int) as database_id from master.sys.tables '; select @sql = @sql + N' union all select ' + quotename(name,'''')+ ', name collate Latin1_General_CI_AI, object_id, schema_id, ' + cast(database_id as nvarchar(10)) + N' from ' + quotename(name) + N'.sys.tables' from sys.databases where database_id > 1 and state = 0 and user_access = 0; exec sp_executesql @sql; 

Necesitaba algo que pudiera usar para buscar en todos mis servidores usando CMS y buscar por servidor, base de datos, esquema o tabla. Esto es lo que encontré (originalmente publicado por Michael Sorens aquí: ¿Cómo puedo enumerar todas las tablas en todas las bases de datos en SQL Server en un solo conjunto de resultados? ).

 SET NOCOUNT ON DECLARE @AllTables TABLE ( ServerName NVARCHAR(200) ,DBName NVARCHAR(200) ,SchemaName NVARCHAR(200) ,TableName NVARCHAR(200) ) DECLARE @SearchSvr NVARCHAR(200) ,@SearchDB NVARCHAR(200) ,@SearchS NVARCHAR(200) ,@SearchTbl NVARCHAR(200) ,@SQL NVARCHAR(4000) SET @SearchSvr = NULL --Search for Servers, NULL for all Servers SET @SearchDB = NULL --Search for DB, NULL for all Databases SET @SearchS = NULL --Search for Schemas, NULL for all Schemas SET @SearchTbl = NULL --Search for Tables, NULL for all Tables SET @SQL = 'SELECT @@SERVERNAME ,''?'' ,s.name ,t.name FROM [?].sys.tables t JOIN sys.schemas s on t.schema_id=s.schema_id WHERE @@SERVERNAME LIKE ''%' + ISNULL(@SearchSvr, '') + '%'' AND ''?'' LIKE ''%' + ISNULL(@SearchDB, '') + '%'' AND s.name LIKE ''%' + ISNULL(@SearchS, '') + '%'' AND t.name LIKE ''%' + ISNULL(@SearchTbl, '') + '%'' -- AND ''?'' NOT IN (''master'',''model'',''msdb'',''tempdb'',''SSISDB'') ' -- Remove the '--' from the last statement in the WHERE clause to exclude system tables INSERT INTO @AllTables ( ServerName ,DBName ,SchemaName ,TableName ) EXEC sp_MSforeachdb @SQL SET NOCOUNT OFF SELECT * FROM @AllTables ORDER BY 1,2,3,4 

Publiqué una respuesta hace un tiempo que podrías usar aquí. El esquema es:

  • Crea una tabla temporal
  • Llamar a sp_msForEachDb
  • La consulta ejecutada contra cada DB almacena los datos en la tabla temporal
  • Cuando termine, consulte la tabla temporal

Me gusta bastante usar INFORMATION_SCHEMA para esto ya que recibo el nombre de la base de datos de forma gratuita. Eso y, al darme cuenta de que @KM publica que los conjuntos de resultados múltiples se insertan muy bien, se me ocurrió:

 select top 0 * into #temp from INFORMATION_SCHEMA.TABLES insert into #temp exec sp_msforeachdb 'select * from [?].INFORMATION_SCHEMA.TABLES' select * from #temp drop table #temp 

Creo que el enfoque común es SELECT * FROM INFORMATION_SCHEMA.TABLES para cada base de datos usando sp_MSforeachdb

Creé un fragmento en VS Code que creo que podría ser útil.

Consulta

 IF OBJECT_ID('tempdb..#alltables', 'U') IS NOT NULL DROP TABLE #alltables; SELECT * INTO #alltables FROM INFORMATION_SCHEMA.TABLES; TRUNCATE TABLE #alltables; EXEC sp_MSforeachdb 'USE [?];INSERT INTO #alltables SELECT * from INFORMATION_SCHEMA.TABLES'; SELECT * FROM #alltables WHERE TABLE_NAME LIKE '%%'; GO 

Retazo

 { "List all tables": { "prefix": "sqlListTable", "body": [ "IF OBJECT_ID('tempdb..#alltables', 'U') IS NOT NULL DROP TABLE #alltables;", "SELECT * INTO #alltables FROM INFORMATION_SCHEMA.TABLES;", "TRUNCATE TABLE #alltables;", "EXEC sp_MSforeachdb 'USE [?];INSERT INTO #alltables SELECT * from INFORMATION_SCHEMA.TABLES';", "SELECT * FROM #alltables WHERE TABLE_NAME LIKE '%$0%';", "GO" ] } } 

Estoy bastante seguro de que tendrás que recorrer la lista de bases de datos y luego enumerar cada tabla. Deberías poder unirlos juntos.

Todo lo que necesita hacer es ejecutar el procedimiento sp_tables almacenado. http://msdn.microsoft.com/en-us/library/aa260318(SQL.80).aspx

Esto es muy útil, pero quería una forma de mostrar todos los objetos del usuario, no solo las tablas, así que lo adapté para usar sys.objects en lugar de sys.tables

 SET NOCOUNT ON DECLARE @AllTables table (DbName sysname,SchemaName sysname, ObjectType char(2), ObjectName sysname) DECLARE @SearchDb nvarchar(200) ,@SearchSchema nvarchar(200) ,@SearchObject nvarchar(200) ,@SQL nvarchar(4000) SET @SearchDb='%' SET @SearchSchema='%' SET @SearchObject='%Something%' SET @SQL='select ''?'' as DbName, s.name as SchemaName, t.type as ObjectType, t.name as ObjectName from [?].sys.objects t inner join sys.schemas s on t.schema_id=s.schema_id WHERE t.type in (''FN'',''IF'',''U'',''V'',''P'',''TF'') AND ''?'' LIKE '''+@SearchDb+''' AND s.name LIKE '''+@SearchSchema+''' AND t.name LIKE '''+@SearchObject+'''' INSERT INTO @AllTables (DbName, SchemaName, ObjectType, ObjectName) EXEC sp_msforeachdb @SQL SET NOCOUNT OFF SELECT * FROM @AllTables ORDER BY DbName, SchemaName, ObjectType, ObjectName 

Me doy cuenta de que este es un hilo muy antiguo, pero fue muy útil cuando tuve que armar algunos documentos del sistema para varios servidores diferentes que alojaban diferentes versiones de Sql Server. Terminé creando 4 procedimientos almacenados que publico aquí para el beneficio de la comunidad. Usamos Dynamics NAV para que los dos procedimientos almacenados con NAV en el nombre dividan la compañía Nav del nombre de la tabla. Disfrutar…

1 de 4 – ListServerDatabases

 USE [YourDatabase] GO /****** Object: StoredProcedure [pssi].[ListServerDatabases] Script Date: 10/3/2017 8:56:45 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROC [dbo].[ListServerDatabases] ( @SearchDatabases varchar(max) = NULL, @ExcludeSystemDatabases bit = 1, @Sql varchar(max) OUTPUT ) AS BEGIN /************************************************************************************************************************************** * Lists all of the databases for a given server. * Parameters * SearchDatabases - Comma delimited list of database names for which to search - converted into series of Like statements * Defaults to null * ExcludeSystemDatabases - 1 to exclude system databases, otherwise 0 * Defaults to 1 * Sql - Output - the stored proc generated sql * * Adapted from answer by * From: How do I list all tables in all databases in SQL Server in a single result set? * Link: https://stackoverflow.com/questions/2875768/how-do-i-list-all-tables-in-all-databases-in-sql-server-in-a-single-result-set * **************************************************************************************************************************************/ SET NOCOUNT ON DECLARE @l_CompoundLikeStatement varchar(max) = '' DECLARE @l_DatabaseName sysname DECLARE @l_Index int DECLARE @lUseAndText bit = 0 DECLARE @l_AllDatabases table (ServerName sysname, DbName sysname) SET @Sql = 'select @@ServerName as ''ServerName'', ''?'' as ''DbName''' IF @SearchDatabases IS NOT NULL BEGIN SET @l_CompoundLikeStatement = char(13) + 'where (' + char(13) WHILE LEN(LTRIM(RTRIM(@SearchDatabases))) > 0 BEGIN SET @l_Index = CHARINDEX(',', @SearchDatabases) IF @l_Index = 0 BEGIN SET @l_DatabaseName = LTRIM(RTRIM(@SearchDatabases)) END ELSE BEGIN SET @l_DatabaseName = LTRIM(RTRIM(LEFT(@SearchDatabases, @l_Index - 1))) END SET @SearchDatabases = LTRIM(RTRIM(REPLACE(LTRIM(RTRIM(REPLACE(@SearchDatabases, @l_DatabaseName, ''))), ',', ''))) SET @l_CompoundLikeStatement = @l_CompoundLikeStatement + char(13) + ' ''?'' like ''' + @l_DatabaseName + '%'' COLLATE Latin1_General_CI_AS or ' END -- Trim trailing Or and add closing right parenthesis ) SET @l_CompoundLikeStatement = LTRIM(RTRIM(@l_CompoundLikeStatement)) SET @l_CompoundLikeStatement = LEFT(@l_CompoundLikeStatement, LEN(@l_CompoundLikeStatement) - 2) + ' )' SET @Sql = @Sql + char(13) + @l_CompoundLikeStatement SET @lUseAndText = 1 END IF @ExcludeSystemDatabases = 1 BEGIN SET @Sql = @Sql + char(13) SET @Sql = @Sql + case when @lUseAndText = 1 THEN ' and ' ELSE 'where ' END + '''?'' not in (''master'' COLLATE Latin1_General_CI_AS, ''model'' COLLATE Latin1_General_CI_AS, ''msdb'' COLLATE Latin1_General_CI_AS, ''tempdb'' COLLATE Latin1_General_CI_AS)' END /* PRINT @Sql */ INSERT INTO @l_AllDatabases EXEC sp_msforeachdb @Sql SELECT * FROM @l_AllDatabases ORDER BY DbName END 

Me doy cuenta de que este es un hilo muy antiguo, pero fue muy útil cuando tuve que armar algunos documentos del sistema para varios servidores diferentes que alojaban diferentes versiones de Sql Server. Terminé creando 4 procedimientos almacenados que publico aquí para el beneficio de la comunidad. Usamos Dynamics NAV para que los dos procedimientos almacenados con NAV en el nombre dividan la compañía Nav del nombre de la tabla. Disfrutar…

2 de 4 – ListServerDatabaseTables

 USE [YourDatabase] GO SET QUOTED_IDENTIFIER ON GO ALTER PROC [dbo].[ListServerDatabaseTables] ( @SearchDatabases varchar(max) = NULL, @SearchSchema sysname = NULL, @SearchTables varchar(max) = NULL, @ExcludeSystemDatabases bit = 1, @Sql varchar(max) OUTPUT ) AS BEGIN /************************************************************************************************************************************** * Lists all of the database tables for a given server. * Parameters * SearchDatabases - Comma delimited list of database names for which to search - converted into series of Like statements * Defaults to null * SearchSchema - Schema name for which to search * Defaults to null * SearchTables - Comma delimited list of table names for which to search - converted into series of Like statements * Defaults to null * ExcludeSystemDatabases - 1 to exclude system databases, otherwise 0 * Defaults to 1 * Sql - Output - the stored proc generated sql * * Adapted from answer by KM answered May 21 '10 at 13:33 * From: How do I list all tables in all databases in SQL Server in a single result set? * Link: https://stackoverflow.com/questions/2875768/how-do-i-list-all-tables-in-all-databases-in-sql-server-in-a-single-result-set * **************************************************************************************************************************************/ SET NOCOUNT ON DECLARE @l_CompoundLikeStatement varchar(max) = '' DECLARE @l_TableName sysname DECLARE @l_DatabaseName sysname DECLARE @l_Index int DECLARE @l_UseAndText bit = 0 DECLARE @AllTables table (ServerName sysname, DbName sysname, SchemaName sysname, TableName sysname) SET @Sql = 'select @@ServerName as ''ServerName'', ''?'' as ''DbName'', s.name as ''SchemaName'', t.name as ''TableName'' ' + char(13) + 'from [?].sys.tables t inner join ' + char(13) + ' sys.schemas s on t.schema_id = s.schema_id ' -- Comma delimited list of database names for which to search IF @SearchDatabases IS NOT NULL BEGIN SET @l_CompoundLikeStatement = char(13) + 'where (' + char(13) WHILE LEN(LTRIM(RTRIM(@SearchDatabases))) > 0 BEGIN SET @l_Index = CHARINDEX(',', @SearchDatabases) IF @l_Index = 0 BEGIN SET @l_DatabaseName = LTRIM(RTRIM(@SearchDatabases)) END ELSE BEGIN SET @l_DatabaseName = LTRIM(RTRIM(LEFT(@SearchDatabases, @l_Index - 1))) END SET @SearchDatabases = LTRIM(RTRIM(REPLACE(LTRIM(RTRIM(REPLACE(@SearchDatabases, @l_DatabaseName, ''))), ',', ''))) SET @l_CompoundLikeStatement = @l_CompoundLikeStatement + char(13) + ' ''?'' like ''' + @l_DatabaseName + '%'' COLLATE Latin1_General_CI_AS or ' END -- Trim trailing Or and add closing right parenthesis ) SET @l_CompoundLikeStatement = LTRIM(RTRIM(@l_CompoundLikeStatement)) SET @l_CompoundLikeStatement = LEFT(@l_CompoundLikeStatement, LEN(@l_CompoundLikeStatement) - 2) + ')' SET @Sql = @Sql + char(13) + @l_CompoundLikeStatement SET @l_UseAndText = 1 END -- Search schema IF @SearchSchema IS NOT NULL BEGIN SET @Sql = @Sql + char(13) SET @Sql = @Sql + CASE WHEN @l_UseAndText = 1 THEN ' and ' ELSE 'where ' END + 's.name LIKE ''' + @SearchSchema + ''' COLLATE Latin1_General_CI_AS' SET @l_UseAndText = 1 END -- Comma delimited list of table names for which to search IF @SearchTables IS NOT NULL BEGIN SET @l_CompoundLikeStatement = char(13) + CASE WHEN @l_UseAndText = 1 THEN ' and (' ELSE 'where (' END + char(13) WHILE LEN(LTRIM(RTRIM(@SearchTables))) > 0 BEGIN SET @l_Index = CHARINDEX(',', @SearchTables) IF @l_Index = 0 BEGIN SET @l_TableName = LTRIM(RTRIM(@SearchTables)) END ELSE BEGIN SET @l_TableName = LTRIM(RTRIM(LEFT(@SearchTables, @l_Index - 1))) END SET @SearchTables = LTRIM(RTRIM(REPLACE(LTRIM(RTRIM(REPLACE(@SearchTables, @l_TableName, ''))), ',', ''))) SET @l_CompoundLikeStatement = @l_CompoundLikeStatement + char(13) + ' t.name like ''$' + @l_TableName + ''' COLLATE Latin1_General_CI_AS or ' END -- Trim trailing Or and add closing right parenthesis ) SET @l_CompoundLikeStatement = LTRIM(RTRIM(@l_CompoundLikeStatement)) SET @l_CompoundLikeStatement = LEFT(@l_CompoundLikeStatement, LEN(@l_CompoundLikeStatement) - 2) + ' )' SET @Sql = @Sql + char(13) + @l_CompoundLikeStatement SET @l_UseAndText = 1 END IF @ExcludeSystemDatabases = 1 BEGIN SET @Sql = @Sql + char(13) SET @Sql = @Sql + case when @l_UseAndText = 1 THEN ' and ' ELSE 'where ' END + '''?'' not in (''master'' COLLATE Latin1_General_CI_AS, ''model'' COLLATE Latin1_General_CI_AS, ''msdb'' COLLATE Latin1_General_CI_AS, ''tempdb'' COLLATE Latin1_General_CI_AS)' END /* PRINT @Sql */ INSERT INTO @AllTables EXEC sp_msforeachdb @Sql SELECT * FROM @AllTables ORDER BY DbName COLLATE Latin1_General_CI_AS, SchemaName COLLATE Latin1_General_CI_AS, TableName COLLATE Latin1_General_CI_AS END 

Me doy cuenta de que este es un hilo muy antiguo, pero fue muy útil cuando tuve que armar algunos documentos del sistema para varios servidores diferentes que alojaban diferentes versiones de Sql Server. Terminé creando 4 procedimientos almacenados que publico aquí para el beneficio de la comunidad. Usamos Dynamics NAV para que los dos procedimientos almacenados con NAV en el nombre dividan la compañía Nav del nombre de la tabla. Disfrutar…

3 de 4 – ListServerDatabaseNavCompanies – para Dynamics NAV

 USE [YourDatabase] GO SET QUOTED_IDENTIFIER ON GO ALTER PROC [dbo].[ListServerDatabaseNavCompanies] ( @SearchDatabases varchar(max) = NULL, @SearchSchema sysname = NULL, @SearchCompanies varchar(max) = NULL, @OrderByDatabaseNameFirst bit = 1, @ExcludeSystemDatabases bit = 1, @Sql varchar(max) OUTPUT ) AS BEGIN /************************************************************************************************************************************** * Lists all of the database companies for a given server. * Parameters * SearchDatabases - Comma delimited list of database names for which to search - converted into series of Like statements * Defaults to null * SearchSchema - Schema name for which to search * Defaults to null * SearchCompanies - Comma delimited list of company names for which to search - converted into series of Like statements * Defaults to null * OrderByDatabaseNameFirst - 1 to sort by Database name and then Company Name, otherwise 0 to sort by Company name first * Defaults to 1 * ExcludeSystemDatabases - 1 to exclude system databases, otherwise 0 * Defaults to 1 * Sql - Output - the stored proc generated sql * * Adapted from answer by KM answered May 21 '10 at 13:33 * From: How do I list all tables in all databases in SQL Server in a single result set? * Link: https://stackoverflow.com/questions/2875768/how-do-i-list-all-tables-in-all-databases-in-sql-server-in-a-single-result-set * **************************************************************************************************************************************/ SET NOCOUNT ON DECLARE @l_CompoundLikeStatement varchar(max) = '' DECLARE @l_CompanyName sysname DECLARE @l_DatabaseName sysname DECLARE @l_Index int DECLARE @l_UseAndText bit = 0 DECLARE @l_Companies table (ServerName sysname, DbName sysname, SchemaName sysname, CompanyName sysname) SET @Sql = 'select distinct @@ServerName as ''ServerName'', ''?'' as ''DbName'', s.name as ''SchemaName'', ' + char(13) + 'case when charindex(''$'', t.name) = 0 then '''' else left(t.name, charindex(''$'', t.name) - 1) end as ''CompanyName''' + char(13) + 'from [?].sys.tables t inner join ' + char(13) + ' sys.schemas s on t.schema_id = s.schema_id ' -- Comma delimited list of database names for which to search IF @SearchDatabases IS NOT NULL BEGIN SET @l_CompoundLikeStatement = char(13) + 'where (' + char(13) WHILE LEN(LTRIM(RTRIM(@SearchDatabases))) > 0 BEGIN SET @l_Index = CHARINDEX(',', @SearchDatabases) IF @l_Index = 0 BEGIN SET @l_DatabaseName = LTRIM(RTRIM(@SearchDatabases)) END ELSE BEGIN SET @l_DatabaseName = LTRIM(RTRIM(LEFT(@SearchDatabases, @l_Index - 1))) END SET @SearchDatabases = LTRIM(RTRIM(REPLACE(LTRIM(RTRIM(REPLACE(@SearchDatabases, @l_DatabaseName, ''))), ',', ''))) SET @l_CompoundLikeStatement = @l_CompoundLikeStatement + char(13) + ' ''?'' like ''' + @l_DatabaseName + '%'' COLLATE Latin1_General_CI_AS or ' END -- Trim trailing Or and add closing right parenthesis ) SET @l_CompoundLikeStatement = LTRIM(RTRIM(@l_CompoundLikeStatement)) SET @l_CompoundLikeStatement = LEFT(@l_CompoundLikeStatement, LEN(@l_CompoundLikeStatement) - 2) + ')' SET @Sql = @Sql + char(13) + @l_CompoundLikeStatement SET @l_UseAndText = 1 END -- Search schema IF @SearchSchema IS NOT NULL BEGIN SET @Sql = @Sql + char(13) SET @Sql = @Sql + CASE WHEN @l_UseAndText = 1 THEN ' and ' ELSE 'where ' END + 's.name LIKE ''' + @SearchSchema + ''' COLLATE Latin1_General_CI_AS' SET @l_UseAndText = 1 END -- Comma delimited list of company names for which to search IF @SearchCompanies IS NOT NULL BEGIN SET @l_CompoundLikeStatement = char(13) + CASE WHEN @l_UseAndText = 1 THEN ' and (' ELSE 'where (' END + char(13) WHILE LEN(LTRIM(RTRIM(@SearchCompanies))) > 0 BEGIN SET @l_Index = CHARINDEX(',', @SearchCompanies) IF @l_Index = 0 BEGIN SET @l_CompanyName = LTRIM(RTRIM(@SearchCompanies)) END ELSE BEGIN SET @l_CompanyName = LTRIM(RTRIM(LEFT(@SearchCompanies, @l_Index - 1))) END SET @SearchCompanies = LTRIM(RTRIM(REPLACE(LTRIM(RTRIM(REPLACE(@SearchCompanies, @l_CompanyName, ''))), ',', ''))) SET @l_CompoundLikeStatement = @l_CompoundLikeStatement + char(13) + ' t.name like ''' + @l_CompanyName + '%'' COLLATE Latin1_General_CI_AS or ' END -- Trim trailing Or and add closing right parenthesis ) SET @l_CompoundLikeStatement = LTRIM(RTRIM(@l_CompoundLikeStatement)) SET @l_CompoundLikeStatement = LEFT(@l_CompoundLikeStatement, LEN(@l_CompoundLikeStatement) - 2) + ' )' SET @Sql = @Sql + char(13) + @l_CompoundLikeStatement SET @l_UseAndText = 1 END IF @ExcludeSystemDatabases = 1 BEGIN SET @Sql = @Sql + char(13) SET @Sql = @Sql + case when @l_UseAndText = 1 THEN ' and ' ELSE 'where ' END + '''?'' not in (''master'' COLLATE Latin1_General_CI_AS, ''model'' COLLATE Latin1_General_CI_AS, ''msdb'' COLLATE Latin1_General_CI_AS, ''tempdb'' COLLATE Latin1_General_CI_AS)' END /* PRINT @Sql */ INSERT INTO @l_Companies EXEC sp_msforeachdb @Sql SELECT CASE WHEN @OrderByDatabaseNameFirst = 1 THEN 'DbName & CompanyName' ELSE 'CompanyName & DbName' END AS 'Sorted by' SELECT ServerName, DbName COLLATE Latin1_General_CI_AS AS 'DbName', SchemaName COLLATE Latin1_General_CI_AS AS 'SchemaName', CompanyName COLLATE Latin1_General_CI_AS AS 'CompanyName' FROM @l_Companies ORDER BY SchemaName COLLATE Latin1_General_CI_AS, CASE WHEN @OrderByDatabaseNameFirst = 1 THEN DbName COLLATE Latin1_General_CI_AS ELSE CompanyName COLLATE Latin1_General_CI_AS END, CASE WHEN @OrderByDatabaseNameFirst = 1 THEN CompanyName COLLATE Latin1_General_CI_AS ELSE DbName COLLATE Latin1_General_CI_AS END END 

Me doy cuenta de que este es un hilo muy antiguo, pero fue muy útil cuando tuve que armar algunos documentos del sistema para varios servidores diferentes que alojaban diferentes versiones de Sql Server. Terminé creando 4 procedimientos almacenados que publico aquí para el beneficio de la comunidad. Usamos Dynamics NAV para que los dos procedimientos almacenados con NAV en el nombre dividan la compañía Nav del nombre de la tabla. Disfrutar…

4 de 4 – ListServerDatabaseNavTables – para Dynamics NAV

 USE [YourDatabase] GO SET QUOTED_IDENTIFIER ON GO ALTER proc [dbo].[ListServerDatabaseNavTables] ( @SearchDatabases varchar(max) = NULL, @SearchSchema sysname = NULL, @SearchCompanies varchar(max) = NULL, @SearchTables varchar(max) = NULL, @ExcludeSystemDatabases bit = 1, @Sql varchar(max) OUTPUT ) AS BEGIN /************************************************************************************************************************************** * Lists all of the database tables for a given server. * Parameters * SearchDatabases - Comma delimited list of database names for which to search - converted into series of Like statements * Defaults to null * SearchSchema - Schema name for which to search * Defaults to null * SearchCompanies - Comma delimited list of company names for which to search - converted into series of Like statements * Defaults to null * SearchTables - Comma delimited list of table names for which to search - converted into series of Like statements * Defaults to null * ExcludeSystemDatabases - 1 to exclude system databases, otherwise 0 * Defaults to 1 * Sql - Output - the stored proc generated sql * * Adapted from answer by KM answered May 21 '10 at 13:33 * From: How do I list all tables in all databases in SQL Server in a single result set? * Link: https://stackoverflow.com/questions/2875768/how-do-i-list-all-tables-in-all-databases-in-sql-server-in-a-single-result-set * **************************************************************************************************************************************/ SET NOCOUNT ON DECLARE @l_CompoundLikeStatement varchar(max) = '' DECLARE @l_TableName sysname DECLARE @l_CompanyName sysname DECLARE @l_DatabaseName sysname DECLARE @l_Index int DECLARE @l_UseAndText bit = 0 DECLARE @AllTables table (ServerName sysname, DbName sysname, SchemaName sysname, CompanyName sysname, TableName sysname, NavTableName sysname) SET @Sql = 'select @@ServerName as ''ServerName'', ''?'' as ''DbName'', s.name as ''SchemaName'', ' + char(13) + ' case when charindex(''$'', t.name) = 0 then '''' else left(t.name, charindex(''$'', t.name) - 1) end as ''CompanyName'', ' + char(13) + ' case when charindex(''$'', t.name) = 0 then t.name else substring(t.name, charindex(''$'', t.name) + 1, 1000) end as ''TableName'', ' + char(13) + ' t.name as ''NavTableName'' ' + char(13) + 'from [?].sys.tables t inner join ' + char(13) + ' sys.schemas s on t.schema_id = s.schema_id ' -- Comma delimited list of database names for which to search IF @SearchDatabases IS NOT NULL BEGIN SET @l_CompoundLikeStatement = char(13) + 'where (' + char(13) WHILE LEN(LTRIM(RTRIM(@SearchDatabases))) > 0 BEGIN SET @l_Index = CHARINDEX(',', @SearchDatabases) IF @l_Index = 0 BEGIN SET @l_DatabaseName = LTRIM(RTRIM(@SearchDatabases)) END ELSE BEGIN SET @l_DatabaseName = LTRIM(RTRIM(LEFT(@SearchDatabases, @l_Index - 1))) END SET @SearchDatabases = LTRIM(RTRIM(REPLACE(LTRIM(RTRIM(REPLACE(@SearchDatabases, @l_DatabaseName, ''))), ',', ''))) SET @l_CompoundLikeStatement = @l_CompoundLikeStatement + char(13) + ' ''?'' like ''' + @l_DatabaseName + '%'' COLLATE Latin1_General_CI_AS or ' END -- Trim trailing Or and add closing right parenthesis ) SET @l_CompoundLikeStatement = LTRIM(RTRIM(@l_CompoundLikeStatement)) SET @l_CompoundLikeStatement = LEFT(@l_CompoundLikeStatement, LEN(@l_CompoundLikeStatement) - 2) + ')' SET @Sql = @Sql + char(13) + @l_CompoundLikeStatement SET @l_UseAndText = 1 END -- Search schema IF @SearchSchema IS NOT NULL BEGIN SET @Sql = @Sql + char(13) SET @Sql = @Sql + CASE WHEN @l_UseAndText = 1 THEN ' and ' ELSE 'where ' END + 's.name LIKE ''' + @SearchSchema + ''' COLLATE Latin1_General_CI_AS' SET @l_UseAndText = 1 END -- Comma delimited list of company names for which to search IF @SearchCompanies IS NOT NULL BEGIN SET @l_CompoundLikeStatement = char(13) + CASE WHEN @l_UseAndText = 1 THEN ' and (' ELSE 'where (' END + char(13) WHILE LEN(LTRIM(RTRIM(@SearchCompanies))) > 0 BEGIN SET @l_Index = CHARINDEX(',', @SearchCompanies) IF @l_Index = 0 BEGIN SET @l_CompanyName = LTRIM(RTRIM(@SearchCompanies)) END ELSE BEGIN SET @l_CompanyName = LTRIM(RTRIM(LEFT(@SearchCompanies, @l_Index - 1))) END SET @SearchCompanies = LTRIM(RTRIM(REPLACE(LTRIM(RTRIM(REPLACE(@SearchCompanies, @l_CompanyName, ''))), ',', ''))) SET @l_CompoundLikeStatement = @l_CompoundLikeStatement + char(13) + ' t.name like ''' + @l_CompanyName + '%'' COLLATE Latin1_General_CI_AS or ' END -- Trim trailing Or and add closing right parenthesis ) SET @l_CompoundLikeStatement = LTRIM(RTRIM(@l_CompoundLikeStatement)) SET @l_CompoundLikeStatement = LEFT(@l_CompoundLikeStatement, LEN(@l_CompoundLikeStatement) - 2) + ' )' SET @Sql = @Sql + char(13) + @l_CompoundLikeStatement SET @l_UseAndText = 1 END -- Comma delimited list of table names for which to search IF @SearchTables IS NOT NULL BEGIN SET @l_CompoundLikeStatement = char(13) + CASE WHEN @l_UseAndText = 1 THEN ' and (' ELSE 'where (' END + char(13) WHILE LEN(LTRIM(RTRIM(@SearchTables))) > 0 BEGIN SET @l_Index = CHARINDEX(',', @SearchTables) IF @l_Index = 0 BEGIN SET @l_TableName = LTRIM(RTRIM(@SearchTables)) END ELSE BEGIN SET @l_TableName = LTRIM(RTRIM(LEFT(@SearchTables, @l_Index - 1))) END SET @SearchTables = LTRIM(RTRIM(REPLACE(LTRIM(RTRIM(REPLACE(@SearchTables, @l_TableName, ''))), ',', ''))) SET @l_CompoundLikeStatement = @l_CompoundLikeStatement + char(13) + ' t.name like ''$' + @l_TableName + ''' COLLATE Latin1_General_CI_AS or ' END -- Trim trailing Or and add closing right parenthesis ) SET @l_CompoundLikeStatement = LTRIM(RTRIM(@l_CompoundLikeStatement)) SET @l_CompoundLikeStatement = LEFT(@l_CompoundLikeStatement, LEN(@l_CompoundLikeStatement) - 2) + ' )' SET @Sql = @Sql + char(13) + @l_CompoundLikeStatement SET @l_UseAndText = 1 END IF @ExcludeSystemDatabases = 1 BEGIN SET @Sql = @Sql + char(13) SET @Sql = @Sql + case when @l_UseAndText = 1 THEN ' and ' ELSE 'where ' END + '''?'' not in (''master'' COLLATE Latin1_General_CI_AS, ''model'' COLLATE Latin1_General_CI_AS, ''msdb'' COLLATE Latin1_General_CI_AS, ''tempdb'' COLLATE Latin1_General_CI_AS)' END /* PRINT @Sql */ INSERT INTO @AllTables EXEC sp_msforeachdb @Sql SELECT * FROM @AllTables ORDER BY DbName COLLATE Latin1_General_CI_AS, CompanyName COLLATE Latin1_General_CI_AS, TableName COLLATE Latin1_General_CI_AS END 

Here’s a tutorial providing a T-SQL script that will return the following fields for each table from each database located in a SQL Server Instance:

  1. ServerName
  2. Nombre de la base de datos
  3. SchemaName
  4. Nombre de la tabla
  5. ColumnName
  6. KeyType

https://tidbytez.com/2015/06/01/map-the-table-structure-of-a-sql-server-database/

 /* SCRIPT UPDATED 20180316 */ USE [master] GO /*DROP TEMP TABLES IF THEY EXIST*/ IF OBJECT_ID('tempdb..#DatabaseList') IS NOT NULL DROP TABLE #DatabaseList; IF OBJECT_ID('tempdb..#TableStructure') IS NOT NULL DROP TABLE #TableStructure; IF OBJECT_ID('tempdb..#ErrorTable') IS NOT NULL DROP TABLE #ErrorTable; IF OBJECT_ID('tempdb..#MappedServer') IS NOT NULL DROP TABLE #MappedServer; DECLARE @ServerName AS SYSNAME SET @ServerName = @@SERVERNAME CREATE TABLE #DatabaseList ( Id INT NOT NULL IDENTITY(1, 1) PRIMARY KEY ,ServerName SYSNAME ,DbName SYSNAME ); CREATE TABLE [#TableStructure] ( [DbName] SYSNAME ,[SchemaName] SYSNAME ,[TableName] SYSNAME ,[ColumnName] SYSNAME ,[KeyType] CHAR(7) ) ON [PRIMARY]; /*THE ERROR TABLE WILL STORE THE DYNAMIC SQL THAT DID NOT WORK*/ CREATE TABLE [#ErrorTable] ([SqlCommand] VARCHAR(MAX)) ON [PRIMARY]; /* A LIST OF DISTINCT DATABASE NAMES IS CREATED THESE TWO COLUMNS ARE STORED IN THE #DatabaseList TEMP TABLE THIS TABLE IS USED IN A FOR LOOP TO GET EACH DATABASE NAME */ INSERT INTO #DatabaseList ( ServerName ,DbName ) SELECT @ServerName ,NAME AS DbName FROM master.dbo.sysdatabases WITH (NOLOCK) WHERE NAME <> 'tempdb' ORDER BY NAME ASC /*VARIABLES ARE DECLARED FOR USE IN THE FOLLOWING FOR LOOP*/ DECLARE @sqlCommand AS VARCHAR(MAX) DECLARE @DbName AS SYSNAME DECLARE @i AS INT DECLARE @z AS INT SET @i = 1 SET @z = ( SELECT COUNT(*) + 1 FROM #DatabaseList ) /*WHILE 1 IS LESS THAN THE NUMBER OF DATABASE NAMES IN #DatabaseList*/ WHILE @i < @z BEGIN /*GET NEW DATABASE NAME*/ SET @DbName = ( SELECT [DbName] FROM #DatabaseList WHERE Id = @i ) /*CREATE DYNAMIC SQL TO GET EACH TABLE NAME AND COLUMN NAME FROM EACH DATABASE*/ SET @sqlCommand = 'USE [' + @DbName + '];' + ' INSERT INTO [#TableStructure] SELECT DISTINCT' + '''' + @DbName + '''' + ' AS DbName ,SCHEMA_NAME(SCHEMA_ID) AS SchemaName ,T.NAME AS TableName ,C.NAME AS ColumnName ,CASE WHEN OBJECTPROPERTY(OBJECT_ID(iskcu.CONSTRAINT_NAME), ''IsPrimaryKey'') = 1 THEN ''Primary'' WHEN OBJECTPROPERTY(OBJECT_ID(iskcu.CONSTRAINT_NAME), ''IsForeignKey'') = 1 THEN ''Foreign'' ELSE NULL END AS ''KeyType'' FROM SYS.TABLES AS t WITH (NOLOCK) INNER JOIN SYS.COLUMNS C ON T.OBJECT_ID = C.OBJECT_ID LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS iskcu WITH (NOLOCK) ON SCHEMA_NAME(SCHEMA_ID) = iskcu.TABLE_SCHEMA AND T.NAME = iskcu.TABLE_NAME AND C.NAME = iskcu.COLUMN_NAME ORDER BY SchemaName ASC ,TableName ASC ,ColumnName ASC; '; /*ERROR HANDLING*/ BEGIN TRY EXEC (@sqlCommand) END TRY BEGIN CATCH INSERT INTO #ErrorTable SELECT (@sqlCommand) END CATCH SET @i = @i + 1 END /* JOIN THE TEMP TABLES TOGETHER TO CREATE A MAPPED STRUCTURE OF THE SERVER ADDITIONAL FIELDS ARE ADDED TO MAKE SELECTING TABLES AND FIELDS EASIER */ SELECT DISTINCT @@SERVERNAME AS ServerName ,DL.DbName ,TS.SchemaName ,TS.TableName ,TS.ColumnName ,TS.[KeyType] ,',' + QUOTENAME(TS.ColumnName) AS BracketedColumn ,',' + QUOTENAME(TS.TableName) + '.' + QUOTENAME(TS.ColumnName) AS BracketedTableAndColumn ,'SELECT * FROM ' + QUOTENAME(DL.DbName) + '.' + QUOTENAME(TS.SchemaName) + '.' + QUOTENAME(TS.TableName) + '--WHERE --GROUP BY --HAVING --ORDER BY' AS [SelectTable] ,'SELECT ' + QUOTENAME(TS.TableName) + '.' + QUOTENAME(TS.ColumnName) + ' FROM ' + QUOTENAME(DL.DbName) + '.' + QUOTENAME(TS.SchemaName) + '.' + QUOTENAME(TS.TableName) + '--WHERE --GROUP BY --HAVING --ORDER BY' AS [SelectColumn] INTO #MappedServer FROM [#DatabaseList] AS DL INNER JOIN [#TableStructure] AS TS ON DL.DbName = TS.DbName ORDER BY DL.DbName ASC ,TS.SchemaName ASC ,TS.TableName ASC ,TS.ColumnName ASC /* HOUSE KEEPING */ IF OBJECT_ID('tempdb..#DatabaseList') IS NOT NULL DROP TABLE #DatabaseList; IF OBJECT_ID('tempdb..#TableStructure') IS NOT NULL DROP TABLE #TableStructure; SELECT * FROM #ErrorTable; IF OBJECT_ID('tempdb..#ErrorTable') IS NOT NULL DROP TABLE #ErrorTable; /* THE DATA RETURNED CAN NOW BE EXPORTED TO EXCEL USING A FILTERED SEARCH WILL NOW MAKE FINDING FIELDS A VERY EASY PROCESS */ SELECT ServerName ,DbName ,SchemaName ,TableName ,ColumnName ,KeyType ,BracketedColumn ,BracketedTableAndColumn ,SelectColumn ,SelectTable FROM #MappedServer ORDER BY DbName ASC ,SchemaName ASC ,TableName ASC ,ColumnName ASC; 
Intereting Posts