如何在 SQL Server 中查找所有数据库中所有表的列名

Question

我想 在所有数据库 的所有表中查找所有列名。有没有可以为我做的查询？

原文由 Salman A 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

尝试这个：

 select
    o.name,c.name
    from sys.columns            c
        inner join sys.objects  o on c.object_id=o.object_id
    order by o.name,c.column_id

使用结果列名，这将是：

 select
     o.name as [Table], c.name as [Column]
     from sys.columns            c
         inner join sys.objects  o on c.object_id=o.object_id
     --where c.name = 'column you want to find'
     order by o.name,c.name

或者更多细节：

 SELECT
    s.name as ColumnName
        ,sh.name+'.'+o.name AS ObjectName
        ,o.type_desc AS ObjectType
        ,CASE
             WHEN t.name IN ('char','varchar') THEN t.name+'('+CASE WHEN s.max_length<0 then 'MAX' ELSE CONVERT(varchar(10),s.max_length) END+')'
             WHEN t.name IN ('nvarchar','nchar') THEN t.name+'('+CASE WHEN s.max_length<0 then 'MAX' ELSE CONVERT(varchar(10),s.max_length/2) END+')'
            WHEN t.name IN ('numeric') THEN t.name+'('+CONVERT(varchar(10),s.precision)+','+CONVERT(varchar(10),s.scale)+')'
             ELSE t.name
         END AS DataType

        ,CASE
             WHEN s.is_nullable=1 THEN 'NULL'
            ELSE 'NOT NULL'
        END AS Nullable
        ,CASE
             WHEN ic.column_id IS NULL THEN ''
             ELSE ' identity('+ISNULL(CONVERT(varchar(10),ic.seed_value),'')+','+ISNULL(CONVERT(varchar(10),ic.increment_value),'')+')='+ISNULL(CONVERT(varchar(10),ic.last_value),'null')
         END
        +CASE
             WHEN sc.column_id IS NULL THEN ''
             ELSE ' computed('+ISNULL(sc.definition,'')+')'
         END
        +CASE
             WHEN cc.object_id IS NULL THEN ''
             ELSE ' check('+ISNULL(cc.definition,'')+')'
         END
            AS MiscInfo
    FROM sys.columns                           s
        INNER JOIN sys.types                   t ON s.system_type_id=t.user_type_id and t.is_user_defined=0
        INNER JOIN sys.objects                 o ON s.object_id=o.object_id
        INNER JOIN sys.schemas                sh on o.schema_id=sh.schema_id
        LEFT OUTER JOIN sys.identity_columns  ic ON s.object_id=ic.object_id AND s.column_id=ic.column_id
        LEFT OUTER JOIN sys.computed_columns  sc ON s.object_id=sc.object_id AND s.column_id=sc.column_id
        LEFT OUTER JOIN sys.check_constraints cc ON s.object_id=cc.parent_object_id AND s.column_id=cc.parent_column_id
    ORDER BY sh.name+'.'+o.name,s.column_id

编辑

这是获取所有数据库中所有列的基本示例：

 DECLARE @SQL varchar(max)
SET @SQL=''
SELECT @SQL=@SQL+'UNION
select
'''+d.name+'.''+sh.name+''.''+o.name,c.name,c.column_id
from '+d.name+'.sys.columns            c
    inner join '+d.name+'.sys.objects  o on c.object_id=o.object_id
    INNER JOIN '+d.name+'.sys.schemas  sh on o.schema_id=sh.schema_id
'
FROM sys.databases d
SELECT @SQL=RIGHT(@SQL,LEN(@SQL)-5)+'order by 1,3'
--print @SQL
EXEC (@SQL)

编辑 SQL Server 2000 版本

DECLARE @SQL varchar(8000)
SET @SQL=''
SELECT @SQL=@SQL+'UNION
select
'''+d.name+'.''+sh.name+''.''+o.name,c.name,c.colid
from '+d.name+'..syscolumns            c
    inner join sysobjects  o on c.id=o.id
    INNER JOIN sysusers  sh on o.uid=sh.uid
'
FROM master.dbo.sysdatabases d
SELECT @SQL=RIGHT(@SQL,LEN(@SQL)-5)+'order by 1,3'
--print @SQL
EXEC (@SQL)

编辑

根据一些评论，这里是使用 sp_MSforeachdb 的版本：

 sp_MSforeachdb 'select
    ''?'' AS DatabaseName, o.name AS TableName,c.name AS ColumnName
    from sys.columns            c
        inner join ?.sys.objects  o on c.object_id=o.object_id
    --WHERE ''?'' NOT IN (''master'',''msdb'',''tempdb'',''model'')
    order by o.name,c.column_id'

原文由 KM. 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 2

在这里，这是我在当前实例的所有数据库中搜索列名的代码：

 SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      themah
-- Create date: 2022-10-09
-- Description: Looking for a column name in all databases in current instance, and list the db names
-- =============================================
CREATE OR ALTER PROCEDURE [dbo].[FindColumnNameInAllDatabases]
    @columnNameToFind NVARCHAR (100)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @dbName VARCHAR(100)
    DECLARE @queryEachDatabase NVARCHAR(MAX)

    DECLARE dbNamesCursor CURSOR FOR
        SELECT [Name]
        FROM sys.databases AS d
        WHERE d.name NOT IN ('master', 'tempdb', 'model', 'msdb')

    OPEN dbNamesCursor
    FETCH NEXT FROM dbNamesCursor INTO @dbName

    WHILE (@@FETCH_STATUS = 0)
    BEGIN

        SET @queryEachDatabase = N'IF Exists
                                        (SELECT 1 FROM [' + @dbName + '].[INFORMATION_SCHEMA].[Columns] WHERE [Column_Name] Like N''%' + @columnNameToFind + '%'')
                                        BEGIN
                                            PRINT ''' + @dbName + '''
                                        END
                                        '
        --PRINT @queryEachDatabase
        EXEC(@queryEachDatabase)

        FETCH NEXT FROM dbNamesCursor INTO @dbName
    END

    CLOSE dbNamesCursor
    DEALLOCATE dbNamesCursor

END
GO

还有一个用法示例： EXEC dbo.FindColumnNameInAllDb N'firstName'

或者另一种打印查询的有用方法是：

 DECLARE @columnName NVARCHAR(100) = N'firstName'

SELECT
'SELECT * FROM [' + NAME + '].[INFORMATION_SCHEMA].[Columns] WHERE [Column_Name] Like N''%' + @columnName + '%'''
FROM [sys].[databases]

原文由 TheMah 发布，翻译遵循 CC BY-SA 4.0 许可协议