programing tip

SQL Server의 모든 데이터베이스에있는 모든 테이블을 단일 결과 집합에 나열하려면 어떻게합니까?

itbloger 2020. 12. 13. 09:02
반응형

SQL Server의 모든 데이터베이스에있는 모든 테이블을 단일 결과 집합에 나열하려면 어떻게합니까?

SQL Server의 모든 데이터베이스에있는 모든 테이블을 나열하는 T-SQL 코드를 찾고 있습니다 (적어도 SS2005 및 SS2008에서는 SS2000에도 적용하는 것이 좋습니다). 그러나 문제는 단일 결과 집합을 원한다는 것 입니다 . 이것은 Pinal Dave 의 다른 훌륭한 대답을 배제합니다 .

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

위의 저장된 proc 은 데이터베이스 당 하나의 결과 집합을 생성 합니다. 여러 결과 집합을 표시 할 수있는 SSMS와 같은 IDE를 사용하는 경우 괜찮습니다. 그러나 본질적으로 "찾기"도구 인 쿼리를 원하기 때문에 단일 결과 집합을 원합니다. WHERE tablename like '%accounts'다음 과 같은 절을 추가하면 상주하는 데이터베이스에 관계없이 BillAccounts, ClientAccounts 및 VendorAccounts 테이블을 찾을 위치를 알려줍니다. 에.

2010.05.20 업데이트, 약 20 분 후 ...

지금까지 Remus의 대답이 가장 흥미로워 보입니다. 이것을 답변으로 게시하고 나에게 수여하는 대신 DB 이름과 샘플 필터 절을 포함하도록 수정 한 버전을 여기에 게시합니다. 하지만이 시점에서 Remus가 답변에 대한 공로를 인정받을 것 같습니다!

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 업데이트-새로운 프론트 러너!

피드백과 답변은 훌륭했습니다. 끊임없는 협업 참여로 새로운 선두 주자로 이어졌습니다 : 5 월 21 일부터 KM의 답변!

Remus의 솔루션으로 내가 발견 한 문제는 다음과 같습니다.

주요 문제 : 사용자는 데이터 (예 : 필터링 값)에 따라 쿼리가 성공하도록하는 다른 권한을 가지고 있습니다. 필터링 없이 프로덕션 데이터베이스에서 실행 (예 : WHERE절 생략 ) 액세스 권한이없는 여러 DB에서이 오류를 받았습니다.

서버 보안 주체 "msorens"는 현재 보안 컨텍스트에서 "ETLprocDB"데이터베이스에 액세스 할 수 없습니다.

쿼리 는 내 액세스 수준을 벗어난 DB를 건드리지 않는 일부 필터링 절로 성공합니다.

사소한 문제 : 각 데이터베이스에 대한 항목을 누적하는 동안 단일 문자열을 작성하기 때문에 SQL Server 2000 지원으로 쉽게 저하되지 않습니다 (예, 여전히 사용하고 있습니다 ...). 내 시스템으로 약 40 개의 데이터베이스에서 8000 자 표시를 넘었습니다.

사소한 문제 : 중복 코드-루프 설정은 본질적으로 루프 본문을 복제합니다. 나는 그 근거를 이해하지만 그것은 단지 내 애완 동물 일 뿐이다 ...

KM의 대답은 이러한 문제에 영향을받지 않습니다. 저장된 proc sp_msforeachdb은 사용자의 권한을 고려하여 권한 문제를 방지합니다. SS2000으로 코드를 아직 시도하지 않았지만 KM은이를 수행해야하는 조정을 나타냅니다.

나는 개인적 선호도에 따라 KM의 답변에 대한 수정 사항을 게시하고 있습니다. 구체적으로 특별히:

  • 결과 세트에 실제로 아무것도 추가하지 않으므로 서버 이름을 제거했습니다.
  • 이름 구성 요소를 결과 집합의 자체 필드 (db 이름, 스키마 이름 및 테이블 이름)로 분할했습니다.
  • 세 필드 각각에 대해 별도의 필터를 도입했습니다.
  • 세 가지 필드를 기준으로 정렬을 추가했습니다 (기본 설정에 따라 수정할 수 있음).

다음은 KM 코드에 대한 수정입니다 (테이블 이름에만 샘플 필터가 적용됨).

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

서버의 모든 테이블을 가져 오는 간단한 방법은 다음과 같습니다.

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

서버 + 데이터베이스 + 스키마 + 테이블 이름이 포함 된 단일 열을 반환합니다. 샘플 출력 :

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

당신은 SQL 서버 2005 또는 최대되지 않으면, 교체 DECLARE @AllTables table로 CREATE TABLE #AllTables다음 각과 @AllTables와 #AllTables그것을 작동합니다.


여기서 EDIT 는 서버 + 데이터베이스 + 스키마 + 테이블 이름의 일부 또는 일부에서 검색 매개 변수를 사용할 수있는 버전입니다.

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

모든 테이블에 대해 @Search를 NULL로 설정하고 'dbo.users', 'users'또는 '.master.dbo'와 같은 것으로 설정하거나 '.master. %. u'와 같은 와일드 카드를 포함 할 수도 있습니다.

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;

CMS를 사용하여 모든 서버를 검색하고 서버, DB, 스키마 또는 테이블별로 검색하는 데 사용할 수있는 것이 필요했습니다. 이것이 제가 찾은 것입니다 (원래 Michael Sorens가 게시했습니다. 여기 에 SQL Server의 모든 데이터베이스에있는 모든 테이블을 단일 결과 집합에 나열하려면 어떻게합니까? ).

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

여기 에 사용할 수 있는 답변을 여기에 게시했습니다 . 개요는 다음과 같습니다.

  • 임시 테이블 만들기
  • sp_msForEachDb 호출
  • 각 DB에 대해 실행 된 쿼리는 임시 테이블에 데이터를 저장합니다.
  • 완료되면 임시 테이블을 쿼리합니다.

DB 이름을 무료로 얻을 수 있으므로 INFORMATION_SCHEMA를 사용하는 것이 좋습니다. 그와-@KM 게시물에서 여러 결과 세트가 멋지게 삽입된다는 것을 깨달았습니다.

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

일반적인 접근 방식은 SELECT * FROM INFORMATION_SCHEMA.TABLES각 데이터베이스에 대해sp_MSforeachdb

나는 그것이 도움이 될 것이라고 생각하는 VS Code에서 스 니펫을 만들었습니다.

질문

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 '%<TABLE_NAME_TO_SEARCH>%';
GO

단편

{
    "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"
        ]
    }
}

이 스레드가 매우 오래된 스레드라는 것을 알고 있지만 다른 버전의 Sql Server를 호스팅하는 여러 다른 서버에 대한 일부 시스템 문서를 작성해야 할 때 매우 유용했습니다. 결국 커뮤니티의 이익을 위해 여기에 게시하는 4 개의 저장 프로 시저를 만들었습니다. Dynamics NAV를 사용하므로 이름에 NAV가 포함 된 두 개의 저장 프로 시저가 테이블 이름에서 Nav 회사를 분리합니다. 즐겨...

4/4-ListServerDatabaseNavTables-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

데이터베이스 목록을 반복 한 다음 각 테이블을 나열해야 할 것입니다. 그것들을 하나로 합칠 수 있어야합니다.

sp_tables 저장 프로 시저를 실행하기 만하면됩니다. http://msdn.microsoft.com/en-us/library/aa260318(SQL.80).aspx

이것은 정말 편리하지만 테이블뿐만 아니라 모든 사용자 개체를 표시하는 방법을 원했기 때문에 sys.tables 대신 sys.objects를 사용하도록 조정했습니다.

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

이 스레드가 매우 오래된 스레드라는 것을 알고 있지만 다른 버전의 Sql Server를 호스팅하는 여러 다른 서버에 대한 일부 시스템 문서를 작성해야 할 때 매우 유용했습니다. 결국 커뮤니티의 이익을 위해 여기에 게시하는 4 개의 저장 프로 시저를 만들었습니다. Dynamics NAV를 사용하므로 이름에 NAV가 포함 된 두 개의 저장 프로 시저가 테이블 이름에서 Nav 회사를 분리합니다. 즐겨...

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

이 스레드가 매우 오래된 스레드라는 것을 알고 있지만 다른 버전의 Sql Server를 호스팅하는 여러 다른 서버에 대한 일부 시스템 문서를 작성해야 할 때 매우 유용했습니다. 결국 커뮤니티의 이익을 위해 여기에 게시하는 4 개의 저장 프로 시저를 만들었습니다. Dynamics NAV를 사용하므로 이름에 NAV가 포함 된 두 개의 저장 프로 시저가 테이블 이름에서 Nav 회사를 분리합니다. 즐겨...

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

이 스레드가 매우 오래된 스레드라는 것을 알고 있지만 다른 버전의 Sql Server를 호스팅하는 여러 다른 서버에 대한 일부 시스템 문서를 작성해야 할 때 매우 유용했습니다. 결국 커뮤니티의 이익을 위해 여기에 게시하는 4 개의 저장 프로 시저를 만들었습니다. Dynamics NAV를 사용하므로 이름에 NAV가 포함 된 두 개의 저장 프로 시저가 테이블 이름에서 Nav 회사를 분리합니다. 즐겨...

3/4-ListServerDatabaseNavCompanies-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

검색 테이블에 대해 @likeTablename 매개 변수를 채우십시오.

이제이 매개 변수를 % tbltrans %로 설정하여 모든 테이블에 이름에 tbltrans를 포함합니다.

모든 테이블을 표시하려면 @likeTablename을 '%'로 설정하십시오.

declare @AllTableNames nvarchar(max);

select  @AllTableNames=STUFF((select ' SELECT  TABLE_CATALOG collate DATABASE_DEFAULT+''.''+TABLE_SCHEMA collate DATABASE_DEFAULT+''.''+TABLE_NAME collate DATABASE_DEFAULT as tablename FROM '+name+'.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ''BASE TABLE'' union '
 FROM master.sys.databases 
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)') 
,1,1,'');

set @AllTableNames=left(@AllTableNames,len(@AllTableNames)-6)

declare @likeTablename nvarchar(200)='%tbltrans%';
set @AllTableNames=N'select tablename from('+@AllTableNames+N')at where tablename like '''+N'%'+@likeTablename+N'%'+N''''
exec sp_executesql  @AllTableNames

다음은 SQL Server 인스턴스에있는 각 데이터베이스의 각 테이블에 대해 다음 필드를 반환하는 T-SQL 스크립트를 제공하는 자습서입니다.

  1. 서버 이름
  2. 데이터베이스 이름
  3. SchemaName
  4. TableName
  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;

참고 URL : https://stackoverflow.com/questions/2875768/how-do-i-list-all-tables-in-all-databases-in-sql-server-in-a-single-result-set

반응형

'programing tip' 카테고리의 다른 글

TFS를 사용하여 패치 만들기  (0) 2020.12.13
PHP 읽기 전용 속성?  (0) 2020.12.13
최고의 JavaScript 이미지 처리 라이브러리는 무엇입니까?  (0) 2020.12.13
C ++에서 함수에 배열 전달  (0) 2020.12.13
쉘 스크립트에서 특정 줄을 주석 처리하는 방법  (0) 2020.12.13

'programing tip'의 다른글

  • 현재글SQL Server의 모든 데이터베이스에있는 모든 테이블을 단일 결과 집합에 나열하려면 어떻게합니까?

관련글

댓글

티스토리툴바