sqlsql-servert-sqldatabase-schema

How to find column names for all tables in all databases in SQL Server


I want to find all column names in all tables in all databases. Is there a query that can do that for me?


Solution

  • Try this:

    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
    

    With resulting column names this would be:

    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
    

    Or for more detail:

    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
    

    EDIT
    Here is a basic example to get all columns in all databases:

    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)
    

    EDIT SQL Server 2000 version

    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)
    

    EDIT
    Based on some comments, here is a version using 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'