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

Question

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

Iow

I've updated my answer, it will work for all databases now on SQL Server 2000. – Wexford

Answer 1

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'

Wexford

Works on current database, not all databases. – Iow

Can you port this example for SQL Server 2000? In SQL Server 2000 there is no sys object. Would replacing sys.colums with syscolumns help? – Iow

You could also try running the original query with sp_MSforeachdb – Jarid

As Chris W said, although they are undocumented, sp_msforeachdb and sp_msforeachtable would be ideal here. Check blogs.techrepublic.com.com/datacenter/?p=395 for some details. – Schaub

There is no varchar(max) in SQL Server 2000 – Stoddard

I believe your second code block "for more detail" is incorrect. You're joining sys.types on system_type_id and you should be using user_type_id. When I ran your code I got multiple records for the same field when the datatype was geography. It also returned a result of the type hierarchyid and geometry. Also, BOL indicates the correct field to join here: msdn.microsoft.com/en-us/library/ms188021.aspx – Adjutant

@DavidStein, you are correct. It caused problems with nvarchar and nchar as well. This is an old old code fragment, from before those data types existed. – Wexford

The @sp_MSforeachdb version does not work for me... the results differ depending on which database I'm currently using. I added "USE [?] ;" to the beginning of each statement to fix the issue. This could be due to being on SQL Server 2012, I don't have older versions for comparison. – Austinaustina

@Chipmonkey, I fixed that bug. I changed inner join sys.objects to inner join ?.sys.objects. feel free to add square braces if you have special characters in your names. – Wexford

In the "example to get all columns in all databases", it should be 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 – Wives

You don't need "union", "union all" is enough. #50425 – Willaims

Here's my version of the SQL Server 2000 query. I feel it's slightly easier to read and works when databases have different collations. pastebin.com/47vKY1aN – Willaims

Thanks very much! What does the +CASE do? – Lovable

Thanks for the great answer and updates. Suggestion: You should surround the d.name , sh.name and o.name with quoted identifiers usually square brackets. Better, add the QUOTENAME() function around them. You might also need to normalise the collation between each database, e.g. for basic english add COLLATE SQL_Latin1_General_CP1_CI_AS after the part where you are concatenating the database + schema + object name and also after the column name. I had issues with unusual database name (contains a period, SQL thinks it's a linked server) and different database collations – Coorg

@ZachSmith + is just concatenating strings together so the results of the 3 case statements will become one field MiscInfo – Coorg

@Davos, thanks for the suggestions, if you run into problems, yes that is a good way to fix it. I hate square braces, if you need to use them you did something wrong naming your items. – Wexford

Agreed, but sometimes you have to cope with other people's naming choices, and a forgiving interface on your side helps defend against that. Adding QUOTENAME() makes it more re-usable & robust and you don't even have to type any square braces.. – Coorg

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'') and c.name like ''%%job%%'' order by o.name,c.column_id' – Contemporary

Answer 2

Why not use

Select * From INFORMATION_SCHEMA.COLUMNS

You can make it DB specific with

Select * From DBNAME.INFORMATION_SCHEMA.COLUMNS

Schwenk

well one reason is that you want to do this from within a function that doesn't know beforehand the name of the database. – Lovable

So questions are timeless since this is a high ranking Google result. Also. I didn't know that. – Lovable

SQL Server 2000 did have functions, besides all the built in scalar functions, it had OPENQUERY() and also dynamic sql like EXEC('sql string here') which would be valid use cases for not knowing the DBNAME ahead of time. Check the docs microsoft.com/en-us/download/details.aspx?id=51958 This answer is simple enough for some use cases, but to be fair you did pose your answer as a question, and whether rhetorical or not, there are valid reasons why you would not want to use this. – Coorg

I guess 5 years ago zach did not make it clear to you the problem with this query. The question asks how to get the information across all DBs -- your answer is DB specific -- the first one will query the current DB and the second will query a named DB. This is fine if you have one DB, but if you have more than one it does not work. – Moreta

Answer 3

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

You must set your current database name with USE [db_name] before this query.

Caras

The question specifically asks for all databases, not just one. – Di

Answer 4

Better way for you

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%'''

Wombat

Please explain why it is better. See how do I write a good answer.. – Crossbench

I don't really like getting a separate result set for each database. – Willaims

this is the only one that worked for me. KM's version was just returning columns from system tables. Also had to put a [] around the ? in the USE statement to cover database names with spaces – Abundance

Nice one user5576010. Of all the solutions posted here, this one seems pretty comprehensive. – Bodyguard

Great. I put square brackets around "?" to avoid errors on some db names. – Vicinage

Answer 5

Some minor improvements

->previous answers weren't showing all results

->possible to filter on column name by setting the column name variable

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)

Soberminded

You don't need "union", "union all" is enough. #50425 – Willaims

Answer 6

Normally I try to do whatever I can to avoid the use of cursors, but the following query will get you everything you need:

--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

I added a where clause in the main query (ex: B.TABLE_NAME LIKE ''%%'' AND B.COLUMN_NAME LIKE ''%%'') so that you can search for specific tables and/or columns if you want to.

Venetis

Answer 7

To all: Thanks for all the post and comments some are good, but some are better.

The first big script is good because it is delivers just what is needed. The fastest and most detailed is the one suggestion for selecting from INFORMATION_SCHEMA.COLUMNS..

My need was to find all the errant columns of approximately the same name and Several databases.. Sooo, I made my versions of both (see below) ...Either of these two below script work and deliver the goods in seconds.

The assumption in other posts on this link, is that the first code example can be used successfully with for-each-database, is to me, not desirable. This is because the information is within the specific database and the simple use of the "fedb" doesn't produce the correct results, it simply doesn't give access. SOOO to that is why I use a CURSOR to collect the databases and ignore those that are Off-line, which in this case, a utility script, it is a good use of same.

Bottom Line, I read everyone's post, incorporated all the correction from the posts and made what are two very eloquent scripts from others good works. I listed both below and have also placed the script file on my public folder at OneDrive.com which you can access with this link: http://1drv.ms/1vr8yNX

Enjoy ! Hank Freeman

Senior Level - SQL Server DBA - Data Architect

Try them separately...

---------------------------
--- 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   
--------

Malleable

A longer answer is not a better answer. Also, please be consistent in indenting and formatting -- this is a mess. – Moreta

Answer 8

I just realized that the following query would give you all column names from the table in your database (SQL SERVER 2017)

SELECT DISTINCT NAME FROM SYSCOLUMNS 
ORDER BY Name 

OR SIMPLY

SELECT Name FROM SYSCOLUMNS

If you do not care about duplicated names.

Another option is SELECT Column names from INFORMATION_SCHEMA

SELECT DISTINCT column_name  FROM INFORMATION_SCHEMA.COLUMNS
ORDER BY column_name

It is usually more interesting to have the TableName as well as the ColumnName ant the query below does just that.

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

And the results would look like

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

Wallacewallach

None of these answer the question -- which is how to search across multiple DBs – Moreta

Answer 9

I used:

EXEC sp_MSforeachdb 'Use ? Select * From INFORMATION_SCHEMA.COLUMNS '

It seems to work for what I needed.

Eboat

Answer 10

user @KM say best Answer.

I Use This :

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 + '%' ) 

Volsci

This one's the only one that doesn't blow up on my server (probably due to funky named databases with spaces for proprietary junk). – Brickwork

This is great, but is there a way to get the database name, similar to obtaining the SCHEMA_NAME? – Fertilize

tabs, they are evil – Moreta

Answer 11

try the below query

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

gives list of tables containing ID column from all databases.

Lepidopteran

I'm interested -- why use the @Query variable -- it does not add to the answer in any way and just makes it more compliated – Moreta

Answer 12

SELECT sys.columns.name AS ColumnName, tables.name AS TableName 
FROM sys.columns 
     JOIN sys.tables ON sys.columns.object_id = tables.object_id

Subvene

Thanx for your answer, but this query will give you all columns in all tables only in current database, not in all databases. – Streaming

Please add some explanation to your answer such that others can learn from it – Nikitanikki

Upvoted the answer as I Was looking for a way to list the all the tables and their column in the current database. Thanks – Rameau

Answer 13

Minor refinement on KM's solution for those like me who've got collation fun on their DB server....

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)

(Still living in hope that we'll find a way to do this that can be wrapped into a view.)

Supernumerary

You don't need "union", "union all" is enough. #50425 – Willaims

Answer 14

Here, this is my code to search for a column name in all databases in the current instance:

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

And a usage example : EXEC dbo.FindColumnNameInAllDb N'firstName'

Or another useful way to print the queries is this :

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

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

Casmey

Simple one... This is easy and has clarity – Contemporary

Answer 15

My solution for similar task was:

    if object_id('tempdb..##temp_meta') is not null
        drop table ##temp_meta

    create table ##temp_meta(database_name sysname
                             , schema_name sysname
                             , table_name sysname
                             , column_name sysname
                             , column_id tinyint
                             , type_name sysname
                             , max_length int
                             , precision tinyint
                             , scale tinyint
                             , is_nullable bit
                             , is_identity bit)

    exec sp_MSforeachdb '

    if(''?'' not in (''master'', ''msdb'', ''model'', ''tempdb''))
    begin
        USE [?]
        insert into ##temp_meta
        SELECT 
           ''?'',
           s.[name] AS schema_name,
           t.name AS table_name,
           c.[name] as column_name,
           c.column_id,
           ty.name as type_name,
           c.max_length,
           c.precision,
           c.scale,
           c.is_nullable,
           c.is_identity
      FROM sys.columns c
      JOIN sys.types ty
        ON ty.system_type_id = c.system_type_id
      JOIN sys.tables t
        ON c.object_id = t.object_id
      JOIN sys.schemas s
        ON s.schema_id = t.schema_id
        
    end'

Viridity

Answer 16

sp_MSForEachDB @command1='USE ?;

SELECT Table_Catalog ,Table_Schema ,Table_Name ,Column_Name ,Data_Type ,Character_Maximum_Length FROM INFORMATION_SCHEMA.COLUMNS WHERE --Data_Type = ''int'' --AND COLUMN_NAME like ''%ColumnName%'' And Table_name like ''%TableName%'' order by 1,2,3 '

Tropaeolin

Answer 17

Get all columns with table names from ALL DATABASES

With this Python (3.12 tested) function you can get an SQL query to get an union of all the columns from different databases

def sql_all_columns(databases: list[str], collation: str):
    """Prints SQL query to get all columns of all databases"""
    db_sql = "SELECT table_catalog COLLATE {cl} AS db, table_schema COLLATE {cl} AS [schema], table_name COLLATE {cl} AS [table], column_name COLLATE {cl} AS [column] FROM [{db}].INFORMATION_SCHEMA.columns"
    stmt = []
    for db in databases:
        stmt.append(db_sql.format(cl=collation, db=db))

    all_columns = "\n  UNION\n".join(stmt)
    sql = f"""
WITH all_columns AS (
{all_columns}
)
SELECT *
FROM all_columns"""
    print(sql)


sql_all_columns(
    ["database1", "database2", "otherdatabase"],
    collation="SQL_Latin1_General_CP1_CI_AS",
)

The execution will print the following SQL statement

WITH all_columns AS (
SELECT table_catalog COLLATE SQL_Latin1_General_CP1_CI_AS AS db, table_schema COLLATE SQL_Latin1_General_CP1_CI_AS AS [schema], table_name COLLATE SQL_Latin1_General_CP1_CI_AS AS [table], column_name COLLATE SQL_Latin1_General_CP1_CI_AS AS [column] FROM [database1].INFORMATION_SCHEMA.columns
  UNION
SELECT table_catalog COLLATE SQL_Latin1_General_CP1_CI_AS AS db, table_schema COLLATE SQL_Latin1_General_CP1_CI_AS AS [schema], table_name COLLATE SQL_Latin1_General_CP1_CI_AS AS [table], column_name COLLATE SQL_Latin1_General_CP1_CI_AS AS [column] FROM [database2].INFORMATION_SCHEMA.columns
  UNION
SELECT table_catalog COLLATE SQL_Latin1_General_CP1_CI_AS AS db, table_schema COLLATE SQL_Latin1_General_CP1_CI_AS AS [schema], table_name COLLATE SQL_Latin1_General_CP1_CI_AS AS [table], column_name COLLATE SQL_Latin1_General_CP1_CI_AS AS [column] FROM [otherdatabase].INFORMATION_SCHEMA.columns     
)
SELECT *
FROM all_columns

To get ALL the databases with permission:

SELECT name
FROM sys.sysdatabases
WHERE HAS_DBACCESS(name) = 1

You can now copy and paste the names list into the Python script

Desmund