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

programing

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

minimums 2023. 6. 22. 21:39

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

SQL Server의 모든 데이터베이스에 있는 모든 테이블을 나열할 T-SQL 코드를 찾고 있습니다(적어도 SS2005 및 SS2008에서는 SS2000에도 적용하면 좋을 것 같습니다).하지만 단점은 단일 결과 세트를 원한다는 것입니다.이렇게 하면 최종 데이브의 훌륭한 답변은 제외됩니다.

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

위에 저장된 proc는 데이터베이스당 하나의 결과 집합을 생성하며, 이는 여러 결과 집합을 표시할 수 있는 SSMS와 같은 IDE에 있는 경우에도 좋습니다.그러나 기본적으로 "찾기" 도구인 쿼리를 원하기 때문에 단일 결과 세트를 원합니다: 만약 제가 다음과 같은 절을 추가한다면.WHERE tablename like '%accounts'그러면 어떤 데이터베이스에 있든 Bill Accounts, Client Accounts 및 Vendor Accounts 테이블을 어디서 찾을 수 있는지 알려줍니다.

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

지금까지, 리머스의 대답이 가장 흥미로워 보입니다.답변으로 게시하여 직접 수여하는 것이 아니라 DB 이름과 샘플 필터 조항을 포함하도록 수정한 버전을 여기에 게시합니다.하지만 이 시점에서 리머스는 그 대답에 대한 공로를 인정받을 것으로 보입니다!

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의 답변!

다음은 제가 리머스 솔루션을 통해 발견한 문제입니다.

주요 문제:사용자는 데이터(즉, 필터링 값)를 기반으로 쿼리를 성공시키는 다양한 권한을 가집니다.운영 데이터베이스에서 필터링 없이 실행(예: 생략)WHEREclause) 액세스 권한이 없는 여러 DB에서 다음 오류를 수신했습니다.

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

쿼리는 일부 필터링 절(내 액세스 단계 외부의 DB에 닿지 않는 절)과 함께 성공합니다.

사소한 문제:각 데이터베이스에 대한 항목을 축적하는 동시에 단일 문자열을 구축하기 때문에 SQL Server 2000 지원으로 쉽게 다운그레이드할 수 없습니다.제 시스템으로 저는 약 40개의 데이터베이스에서 8,000자를 넘었습니다.

사소한 문제:중복 코드--루프 설정은 본질적으로 루프 본문을 복제합니다.그 이유는 이해하지만 그건 내 불만일 뿐이야...

KM의 대답은 이러한 문제들로 인해 고민하지 않습니다. 프로시저 저장저sp_msforeachdb사용 권한 문제를 방지하기 위해 사용자의 사용 권한을 고려합니다.나는 아직 SS2000으로 코드를 시도해보지 않았지만 KM은 그것을 해야 할 조정을 나타냅니다.

개인적인 취향에 따라 KM의 답변에 대한 수정사항을 다음에 게시합니다.구체적으로:

결과 집합에 서버 이름이 추가되지 않아 서버 이름을 제거했습니다.
결과 집합(db name, schema name, table name)에서 이름 구성요소를 고유 필드로 분할했습니다.
저는 세 개의 필드 각각에 대해 별도의 필터를 도입했습니다.
3개 필드별 정렬을 추가했습니다(사용자의 선호도에 따라 수정 가능).

다음은 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

Server 2005를 합니다.DECLARE @AllTables table와 함께CREATE TABLE #AllTables 매 그다마다@AllTables와 함께#AllTables효과가 있을 겁니다

다음 버전은 서버의 모든 부분 또는 부분에 검색 매개 변수를 사용할 수 있게 해줍니다.+서버+서버+테이블 이름:

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

@모든 테이블에 대해 검색을 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

일반적인 접근 방식은 다음과 같습니다.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"
        ]
    }
}

아까 여기에 당신이 사용할 수 있는 답변을 올렸습니다.개요는 다음과 같습니다.

임시 테이블 만들기
각 DB에 대해 sp_ms 호출
각 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

이 스레드가 매우 오래된 스레드라는 것은 알고 있지만, 여러 버전의 SQL 서버를 호스팅하는 여러 서버에 대한 시스템 설명서를 작성해야 할 때 큰 도움이 되었습니다.저는 결국 지역사회의 이익을 위해 여기에 게시하는 4개의 저장 프로시저를 만들었습니다.우리는 Dynamics NAV를 사용하기 때문에 이름에 NAV가 있는 저장 프로시저 두 개가 Nav 회사를 테이블 이름에서 분리합니다.즐기세요...

4개 중 4개 - ListServerDatabaseNavTables - DynamicsNAV용

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

4개 중 1개 - 서버 데이터베이스 나열

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

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

4개 중 3개 - ListServerDatabaseNavCompanys - DynamicsNAV용

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

Bart Gawrych가 Dataedo 사이트에 게시한 저장 프로시저가 없는 접근 방식에 대한 링크

'여기서 굳이 저장 프로시저를 사용해야 하나요?'라고 자문하다가 이 도움이 되는 글을 발견했습니다. (연결된 페이지 사용자의 피드백에 따라 오프라인 데이터베이스 문제를 해결하기 위해 state=0이 추가되었습니다.)

declare @sql nvarchar(max);

select @sql = 
    (select ' UNION ALL
        SELECT ' +  + quotename(name,'''') + ' as database_name,
               s.name COLLATE DATABASE_DEFAULT
                    AS schema_name,
               t.name COLLATE DATABASE_DEFAULT as table_name 
               FROM '+ quotename(name) + '.sys.tables t
               JOIN '+ quotename(name) + '.sys.schemas s
                    on s.schema_id = t.schema_id'
    from sys.databases 
    where state=0
    order by [name] for xml path(''), type).value('.', 'nvarchar(max)');

set @sql = stuff(@sql, 1, 12, '') + ' order by database_name, 
                                               schema_name,
                                               table_name';

execute (@sql);

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

서버 이름
데이터베이스 이름
스키마 이름
테이블 이름
열 이름
키 유형

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;

저는 KM의 답변을 사용했고 가장 일반적인 사용 사례 중 하나인 이 답변에 열을 추가했습니다.이 데이터를 다른 DQ 용도로 사용할 수 있도록 표에도 삽입합니다.몇 분이 걸렸기 때문에 다른 사람의 시간을 절약하고 싶습니다.여기 있습니다.

SET NOCOUNT ON
DECLARE @AllTables table (DbName sysname,SchemaName sysname, TableName sysname, ColumnName sysname)
DECLARE
     @SearchDb nvarchar(200)
    ,@SearchSchema nvarchar(200)
    ,@SearchTable nvarchar(200)
    ,@SearchColumn nvarchar(200)
    ,@SQL nvarchar(4000)
SET @SearchDb='%'
SET @SearchSchema='%'
SET @SearchTable='%'
SET @SearchColumn='%'
SET @SQL='select ''?'' as DbName, s.name as SchemaName, t.name as TableName, c.name as ColumnName
    from [?].sys.tables t 
    inner join sys.schemas s on t.schema_id=s.schema_id 
    inner join sys.columns c on t.object_id=c.object_id
    WHERE ''?'' LIKE '''+@SearchDb+''' AND s.name LIKE '''+@SearchSchema+''' AND t.name LIKE '''+@SearchTable+''' AND c.name LIKE '''+@SearchColumn+''''

INSERT INTO @AllTables (DbName, SchemaName, TableName, ColumnName)
    EXEC sp_msforeachdb @SQL
SET NOCOUNT OFF
SELECT * into ##DBSchTabCol
FROM @AllTables ORDER BY DbName, SchemaName, TableName, ColumnName

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

'programing' 카테고리의 다른 글

j개체에 등록된 이벤트 핸들러 찾기 (0)	2023.06.22
SQL Server : 식을 데이터 형식 int로 변환하는 동안 산술 오버플로 오류가 발생했습니다. (0)	2023.06.22
SQL 서버에서 외부 키 제약 조건을 제거하는 방법은 무엇입니까? (0)	2023.06.22
Microsoft SQL Server에서 데이터베이스 삭제 및 재생성 (0)	2023.06.22
1L와 1L의 차이점은 무엇입니까? (0)	2023.06.22

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

각종 프로그래밍 정보를 다루는 블로그입니다.

git, spring-boot, reactjs, c, sql-server, css, python, WPF, angularJS, PowerShell, mongodb, ASP.NET, mariadb, ajax, Wordpress, Android, MYSQL, Oracle, Excel, jquery,

Today :
Yesterday :

minimum

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

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

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

2010.05.24 업데이트 -- 새로운 프론트 러너!

쿼리

스니펫

'programing' 카테고리의 다른 글

'programing'의 다른글

티스토리툴바

« 2025/09 »
일	월	화	수	목	금	토
	1	2	3	4	5	6
7	8	9	10	11	12	13
14	15	16	17	18	19	20
21	22	23	24	25	26	27
28	29	30

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

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

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

2010.05.24 업데이트 -- 새로운 프론트 러너!

쿼리

스니펫

'programing' 카테고리의 다른 글

'programing'의 다른글

관련글

티스토리툴바