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

试试这个:

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

结果列名称为: 选择 o.name为[表],c.name为[列] 从系统列C c.object_id=o.object_id上的inner join sys.objects o --其中c.name='要查找的列' 按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'

SELECT * 
FROM information_schema.columns 
WHERE column_name = 'My_Column'

必须使用设置当前数据库名称 USE [db_name] 在此查询之前。

为什么不使用

Select * From INFORMATION_SCHEMA.COLUMNS

你可以用特定的

Select * From DBNAME.INFORMATION_SCHEMA.COLUMNS

更好的方法

sp_MSForEachDB @command1='USE ?;
SELECT 
    Table_Catalog 
    ,Table_Schema
    ,Table_Name
    ,Column_Name
    ,Data_Type
    ,Character_Maximum_Length
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME like ''%ColumnNameHere%'''

用户@km说出最佳答案。

我用这个:

Declare @Table_Name VarChar(100) ,@Column_Name VarChar(100)
Set @Table_Name = ''
Set @Column_Name = ''

Select 
RowNumber = Row_Number() Over( PARTITION BY T.[Name] Order By T.[Name],C.column_id  ),
SCHEMA_NAME( T.schema_id ) As SchemaName ,  
T.[Name] As Table_Name ,
C.[Name] As Field_Name , 
sysType.name ,
C.max_length , C.is_nullable , C.is_identity , C.scale , C.precision  
From Sys.Tables As T
Left Join Sys.Columns As C On ( T.[Object_Id] = C.[Object_Id] )
Left Join sys.types As sysType On ( C.user_type_id = sysType.user_type_id )
Where ( Type = 'U' )
    And ( C.Name Like '%' + @Column_Name + '%' )  
    And ( T.Name Like '%' + @Table_Name + '%' ) 

通常,我会尽我所能避免使用光标,但下面的查询将为您提供所需的一切:

--Declare/Set required variables
DECLARE @vchDynamicDatabaseName AS VARCHAR(MAX),
        @vchDynamicQuery As VARCHAR(MAX),
        @DatabasesCursor CURSOR

SET @DatabasesCursor = Cursor FOR

--Select * useful databases on the server
SELECT name 
FROM sys.databases 
WHERE database_id > 4 
ORDER by name

--Open the Cursor based on the previous select
OPEN @DatabasesCursor
FETCH NEXT FROM @DatabasesCursor INTO @vchDynamicDatabaseName
WHILE @@FETCH_STATUS = 0
   BEGIN

   --Insert the select statement into @DynamicQuery 
   --This query will select the Database name, all tables/views and their columns (in a comma delimited field)
   SET @vchDynamicQuery =
   ('SELECT ''' + @vchDynamicDatabaseName + ''' AS ''Database_Name'',
          B.table_name AS ''Table Name'',
         STUFF((SELECT '', '' + A.column_name
               FROM ' + @vchDynamicDatabaseName + '.INFORMATION_SCHEMA.COLUMNS A
               WHERE A.Table_name = B.Table_Name
               FOR XML PATH(''''),TYPE).value(''(./text())[1]'',''NVARCHAR(MAX)'')
               , 1, 2, '''') AS ''Columns''
   FROM ' + @vchDynamicDatabaseName + '.INFORMATION_SCHEMA.COLUMNS B
   WHERE B.TABLE_NAME LIKE ''%%''
         AND B.COLUMN_NAME LIKE ''%%''
   GROUP BY B.Table_Name
   Order BY 1 ASC')

   --Print @vchDynamicQuery
   EXEC(@vchDynamicQuery)

   FETCH NEXT FROM @DatabasesCursor INTO @vchDynamicDatabaseName
END
CLOSE @DatabasesCursor
DEALLOCATE @DatabasesCursor
GO

我在主查询中添加了一个WHERE子句(如“%%”,b.table_name如“%%”,b.column_name如“%%”),这样您可以根据需要搜索特定的表和/或列。

感谢所有的帖子和评论,有些很好,但有些更好。

第一个大脚本很好,因为它提供了所需的内容。最快和最详细的建议是从信息中选择“schema.columns.”。

我需要找到几乎相同名称和几个数据库的所有出错列。Sooo,我做了我的两个版本(见下文)…下面的两个脚本中的任何一个都可以工作并在几秒钟内交付货物。

在这个链接的其他文章中,假设第一个代码示例可以成功地用于每个数据库,这对我来说是不可取的。这是因为信息在特定的数据库中,“fedb”的简单使用不会产生正确的结果,它只是不提供访问权。因此,我使用一个光标来收集数据库,忽略那些离线的数据库,在本例中,这是一个实用程序脚本,很好地使用了它们。

总之,我读了每个人的帖子,把所有的文章更正了起来,并写出了另外两篇精彩的文章。我在下面列出了这两个文件,并将脚本文件放在了我在OneDrive.com的公用文件夹中,您可以通过此链接访问该文件夹: http://1drv.ms/1vr8yNX

享受吧! 汉克·弗里曼

高级-SQL Server DBA-数据架构师

分开试试……

---------------------------
--- 1st example (works) ---
---------------------------
Declare 
 @DBName sysname
,@SQL_String1 nvarchar(4000)
,@SQL_String2 nvarchar(4000)
,@ColumnName nvarchar(200) 
--set @ColumnName = 'Course_ID' 
-------- Like Trick --------
-- IF you want to add more the @ColumnName so it looks like Course_ID,CourseID
-- then add an additional pairing of +''','''+'NewColumnSearchIDValue'
----------------------------
set @ColumnName = 'Course_ID' +''','''+'CourseID'
--select @ColumnName
-----
Declare @Column_Info table
(
[DatabaseName] nvarchar(128) NULL,
[ColumnName] sysname NULL,
[ObjectName] nvarchar(257) NOT NULL,
[ObjectType] nvarchar(60) NULL,
[DataType] nvarchar(151) NULL,
[Nullable] varchar(8) NOT NULL,
[MiscInfo] nvarchar(MAX) NOT NULL
)
--------------
Begin
    set @SQL_String2 = 'SELECT
     DB_NAME() as ''DatabaseName'',
    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
    into ##Temp_Column_Info
    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
    --------------------------------------------
    --- DBA - Hank 12-Feb-2015 added this specific where statement
    --     where Upper(s.name) like ''COURSE%''
    --   where Upper(s.name) in (''' + @ColumnName + ''')
    --  where Upper(s.name) in (''cycle_Code'')
    -- ORDER BY sh.name+''.''+o.name,s.column_id
    order by 1,2'
--------------------
    Declare DB_cursor CURSOR
    FOR 
         SELECT  name  FROM sys.databases 
        --select * from sys.databases 
        WHERE STATE = 0  
      --  and Name not IN ('master','msdb','tempdb','model','DocxPress')
        and Name not IN ('msdb','tempdb','model','DocxPress')
    Open DB_cursor
    Fetch next from DB_cursor into @DBName
    While @@FETCH_STATUS = 0
    begin 
        --select @DBName as '@DBName';
          Set @SQL_String1 = 'USE [' + @DBName + ']'
          set @SQL_String1 = @SQL_String1 + @SQL_String2
          EXEC sp_executesql @SQL_String1;
        --
        insert into @Column_Info
        select * from ##Temp_Column_Info;
        drop table ##Temp_Column_Info;
        Fetch next From DB_cursor into @DBName
    end
    CLOSE DB_cursor;
    Deallocate DB_cursor;
    ---
    select * from @Column_Info order by 2,3

----------------------------
end
---------------------------

Below is the Second script.. 
---------------------------
--- 2nd example (works) ---
---------------------------
-- This is by far the best/fastes of the lot for what it delivers.
--Select * into dbo.hanktst From Master.INFORMATION_SCHEMA.COLUMNS
--FileID: SCRIPT_Get_Column_info_(INFORMATION_SCHEMA.COLUMNS).sql
----------------------------------------
--FileID: SCRIPT_Get_Column_info_(INFORMATION_SCHEMA.COLUMNS).sql
-- Utility to find all columns in all databases or find specific with a like statement
-- Look at this line to find a: --> set @SQL_String2 = ' select * into ##Temp_Column_Info....
----------------------------------------
---
SET NOCOUNT ON
begin 
 Declare @hanktst TABLE (
    [TABLE_CATALOG]              NVARCHAR(128) NULL
   ,[TABLE_SCHEMA]               NVARCHAR(128) NULL
   ,[TABLE_NAME]                 sysname NOT NULL
   ,[COLUMN_NAME]                sysname NULL
   ,[ORDINAL_POSITION]           INT NULL
   ,[COLUMN_DEFAULT]             NVARCHAR(4000) NULL
   ,[IS_NULLABLE]                VARCHAR(3) NULL
   ,[DATA_TYPE]                  NVARCHAR(128) NULL
   ,[CHARACTER_MAXIMUM_LENGTH]   INT NULL
   ,[CHARACTER_OCTET_LENGTH]     INT NULL
   ,[NUMERIC_PRECISION]          TINYINT NULL
   ,[NUMERIC_PRECISION_RADIX]    SMALLINT NULL
   ,[NUMERIC_SCALE]              INT NULL
   ,[DATETIME_PRECISION]         SMALLINT NULL
   ,[CHARACTER_SET_CATALOG]      sysname NULL
   ,[CHARACTER_SET_SCHEMA]       sysname NULL
   ,[CHARACTER_SET_NAME]         sysname NULL
   ,[COLLATION_CATALOG]          sysname NULL
   ,[COLLATION_SCHEMA]           sysname NULL
   ,[COLLATION_NAME]             sysname NULL
   ,[DOMAIN_CATALOG]             sysname NULL
   ,[DOMAIN_SCHEMA]              sysname NULL
   ,[DOMAIN_NAME]                sysname NULL
   )
       Declare 
      @DBName sysname
      ,@SQL_String2 nvarchar(4000)
      ,@TempRowCnt varchar(20)
      ,@Dbug bit = 0
      Declare DB_cursor CURSOR
      FOR 
           SELECT  name  FROM sys.databases 
          WHERE STATE = 0  
        --  and Name not IN ('master','msdb','tempdb','model','DocxPress')
          and Name not IN ('msdb','tempdb','model','DocxPress')
      Open DB_cursor
      Fetch next from DB_cursor into @DBName
      While @@FETCH_STATUS = 0
        begin 
        set @SQL_String2 = ' select * into ##Temp_Column_Info from [' + @DBName + '].INFORMATION_SCHEMA.COLUMNS 
        where UPPER(Column_Name) like ''COURSE%''
        ;'
          if @Dbug = 1  Select @SQL_String2 as '@SQL_String2';
          EXEC sp_executesql @SQL_String2;
          insert into @hanktst
          select * from ##Temp_Column_Info;
          drop table ##Temp_Column_Info;
         Fetch next From DB_cursor into @DBName
        end
        select * from @hanktst order by 4,2,3
      CLOSE DB_cursor;
      Deallocate DB_cursor;
      set @TempRowCnt = (select cast(count(1) as varchar(10)) from @hanktst )
       Print ('Rows found: '+ @TempRowCnt +'  end ...') 
end   
--------

一些小的改进

->以前的答案未显示所有结果

->可以通过设置列名变量来筛选列名

DECLARE @columnname nvarchar(150)
SET @columnname=''

DECLARE @SQL varchar(max)
SET @SQL=''
SELECT @SQL=@SQL+'UNION
select 
'''+d.name+'.''+sh.name+''.''+o.name COLLATE SQL_Latin1_General_CP1_CI_AS as name,c.name COLLATE SQL_Latin1_General_CP1_CI_AS as columnname,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
    where c.name like ''%'+@columnname+'%'' and sh.name<>''sys'' 
'
FROM sys.databases d
SELECT @SQL=RIGHT(@SQL,LEN(@SQL)-5)+'order by 1,3'
--print @SQL
EXEC (@SQL)

尝试下面的查询

DECLARE @Query VARCHAR(max) 
SELECT @Query = 'USE ? SELECT ''?'' AS DataBaseName,
                                sys.columns.name AS ColumnName  ,
                                sys.tables.name  AS TableName   ,
                                schema_name (sys.tables.schema_Id) AS schemaName
                         FROM sys.columns
                         JOIN sys.tables 
              ON sys.columns.object_id = sys.tables.object_id
              WHERE sys.columns.name = ''id'' '
EXEC SP_MSFOREACHDB @Query

提供包含来自所有数据库的ID列的表的列表。

对KM的解决方案进行了细微的改进,适用于像我这样的在DB服务器上享受排序乐趣的人……

DECLARE @SQL varchar(max)=''

SELECT @SQL=@SQL+'UNION
select 
'''+d.name +'.''+sh.name+''.''+o.name COLLATE SQL_Latin1_General_CP1_CI_AS,c.name COLLATE SQL_Latin1_General_CP1_CI_AS,c.column_id
from '+d.name +'.sys.columns            c
    inner join sys.objects  o on c.object_id=o.object_id
    INNER JOIN 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 2017)

SELECT DISTINCT NAME FROM SYSCOLUMNS 
ORDER BY Name 

或者简单地说

SELECT Name FROM SYSCOLUMNS

如果你不在乎重复的名字。

另一个选项是从中选择列名 INFORMATION_SCHEMA

SELECT DISTINCT column_name  FROM INFORMATION_SCHEMA.COLUMNS
ORDER BY column_name

通常更有趣的是 表名 以及 字段名 Ant下面的查询就是这样做的。

SELECT 
   Object_Name(Id) As TableName,
   Name As ColumnName
FROM SysColumns

结果是

  TableName    ColumnName
0    Table1    column11
1    Table1    Column12
2    Table2    Column21
3    Table2    Column22
4    Table3    Column23