How to Find Column Names for All Tables in All Databases in SQL Server

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

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'

Find a column name from all tables and all databases with specified column name SQL Server

There are times when sp_foreachdb will just skip databases. Remember that it is an undocumented system procedure and as such there may be bugs in it. For something like this I prefer to just some dynamic sql. It isn't much more code than the undocumented cursor but it is more accurate.

declare @SQL nvarchar(max) = ''

select @SQL = @SQL + 'SELECT ''' + d.name + ''' as DatabaseName, t.name as TableName, c.name as ColumnName FROM ' + d.name + '.sys.tables t inner join ' + d.name + '.sys.columns c  ON c.object_id = t.object_id WHERE c.name LIKE ''%CUSTOMERID%'' union all '
from sys.databases d
where d.name not in('master', 'tempdb', 'msdb', 'model', 'ReportServer', 'ReportServerTempDB')

set @SQL = left(@SQL, len(@SQL) - 10)

exec sp_executesql @SQL

How to find all tables and datasets/databases which have a specific column name in big query

I found the solution is to replace the dataset name with region-us instead.

The below works for looking up across tables and datasets

SELECT
    ddl 
FROM
    `project-name`.`region-us`.INFORMATION_SCHEMA.TABLES
WHERE 
    table_name like '%sender%' 
    AND ddl LIKE '%sender_country%' 

The below works for views:

SELECT
    ddl 
FROM
    `project-name`.`region-us`.INFORMATION_SCHEMA.VIEWS
WHERE 
    table_name like '%sender%' 
    AND ddl LIKE '%sender_country%' 

How to search a column name in all tables in a database in SQL Server 2012?

Try the below

Select distinct object_name(object_id), name from sys.columns where name like '%tax%'

or

select table_name, Column_name from Information_Schema.Columns where Column_Name like '%Tax%'

Search for column name in multiple databases on SQL Server

Try it like this:

This solution uses the SP sp_MSforeachdb. This SP allows you, to use the same statement for alle existing databases. The Questionmark stand as place holder for the actual database name.

As we need to get the result in one single table I declare one first and use INSERT INTO SomeTable EXEC SomeSP-syntax to insert the result(s) of a SP into a fitting table (-variable):

Attention This SP is known for skipping some tables. Here's a thread to read more about this (including a solution for one special case).

DECLARE @ColumnName VARCHAR(100)='SomeColumnName';

DECLARE @Command VARCHAR(MAX)='USE [?]; SELECT TABLE_CATALOG
                                              ,TABLE_SCHEMA
                                              ,TABLE_NAME
                                              ,COLUMN_NAME 
                                        FROM INFORMATION_SCHEMA.COLUMNS 
                                        WHERE COLUMN_NAME=''' + @ColumnName + ''';';     

DECLARE @tbl TABLE(
                   TABLE_CATALOG VARCHAR(100)
                  ,TABLE_SCHEMA VARCHAR(100)
                  ,TABLE_NAME VARCHAR(100)
                  ,COLUMN_NAME VARCHAR(100)
                  );

INSERT INTO @tbl
EXECUTE master.sys.sp_MSforeachdb @Command

SELECT * FROM @tbl;

UPDATE Another approach without sp_MSforeachdb

DECLARE @ColumnName VARCHAR(100)='SomeColumnName';

DECLARE @Template VARCHAR(MAX)='USE ***; SELECT TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME=''' + @ColumnName + ''';';  

DECLARE @Command VARCHAR(MAX)=
(
    SELECT REPLACE(@Template,'***', QUOTENAME(name))
    FROM sys.databases
    FOR XML PATH('')
);

DECLARE @tbl TABLE(
                   TABLE_CATALOG VARCHAR(100)
                  ,TABLE_SCHEMA VARCHAR(100)
                  ,TABLE_NAME VARCHAR(100)
                  ,COLUMN_NAME VARCHAR(100)
                  );

INSERT INTO @tbl
EXEC (@Command);

SELECT * FROM @tbl

How to get all tables and column names in SQL?

however your question isn't enough clear but you can get all of it with this this code

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

---

Related Topics