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  The database is Microsoft SQL Server 2000

User · Answer

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

User · Answer

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

User · Answer

Why not use  Select   From INFORMATION SCHEMA COLUMNS   You can make it DB specific with  Select   From DBNAME INFORMATION SCHEMA COLUMNS

User · Answer

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

User · Answer

I used   EXEC sp MSforeachdb  Use   Select   From INFORMATION SCHEMA COLUMNS     It seems to work for what I needed

User · Answer

SELECT sys columns name AS ColumnName  tables name AS TableName  FROM sys columns       JOIN sys tables ON sys columns object id   tables object id

User · Answer

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 lt  gt   sys      FROM sys databases d SELECT  SQL RIGHT  SQL LEN  SQL -5   order by 1 3  --print  SQL EXEC   SQL

User · Answer

SELECT    FROM information schema columns  WHERE column name    My Column    You must set your current database name with USE  db name  before this query

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to sql-server-2000

Examples related to database-schema