How do I list all tables in all databases in SQL Server in a single result set

Question

I am looking for T-SQL code to list all tables in all databases in SQL Server  at least in SS2005 and SS2008  would be nice to also apply to SS2000   The catch  however  is that I would like a single result set  This precludes the otherwise excellent answer from Pinal Dave   sp msforeachdb  select     AS db    from     sys tables    The above stored proc generates one result set per database  which is fine if you are in an IDE like SSMS that can display multiple result sets  However  I want a single result set because I want a query that is essentially a  find  tool  if I add a clause like WHERE tablename like   accounts  then it would tell me where to find my BillAccounts  ClientAccounts  and VendorAccounts tables regardless of which database they reside in     2010 05 20 Update  about 20 minutes later     So far  Remus  answer looks most interesting  Rather than post this as an answer and award it to myself  I am posting a version of it here that I have modified to include the DB name and a sample filter clause  It is looking like Remus will get the credit for the answer  though  at this point   declare  sql nvarchar max   set  sql   N select b name as  DB   a name collate Latin1 General CI AI as  Table   object id  schema id  cast 1 as int  as database id  from master sys tables a join sys databases b on database id 1 where a name like   account       select  sql    sql   N  union all select b name as  DB   a name collate Latin1 General CI AI  object id  schema id      cast database id as nvarchar 10     N  from     quotename name    N  sys tables a join sys databases b on database id     cast database id as nvarchar 10      where a name like   account     from sys databases where database id  gt  1   and state   0 and user access   0   exec sp executesql  sql      2010 05 24 Update -- New Front runner   The feedback and answers have been great  Continued collaborative participation has led to a new frontrunner  KM s answer from May 21    Here are the issues I uncovered with Remus  solution   Major issue  Users have different permissions which leads the query to succeed based on the data  i e  the filtering value   Run on my production database with no filtering  i e  omitting the WHERE clause  I received this error on several DBs that I do not have permission to access      The server principal  msorens  is not able to access the database    ETLprocDB  under the current security context    The query will succeed with some filtering clauses--those that do not touch the DBs outside my access level   Minor issue  Not easily degradable to SQL Server 2000 support  yes  there are still some of us out there using it     because it builds a single string while accumulating entries for each database  With my system  I surpassed the 8000-character mark at around 40 databases   Minor issue  Duplicate code--the loop setup essentially duplicates the loop body  I understand the rationale but it is just a pet peeve of mine     KM s answer is not afflicted by these issues  The stored proc sp msforeachdb takes into account the user s permissions so it avoids permission problems  I have not yet tried the code with SS2000 but KM indicates the adjustments that should do it    I am posting next my modifications to KM s answer based on my personal preferences  Specifically    I have removed the server name as it does not really add anything in the result set   I have split the name components into their own fields in the result set  db name  schema name  and table name   I have introduced separate filters for each of the three fields  I have added sorting by the three fields  which can be modified to your preferences     Here is my modification to KM s code  with a sample filter applied just to the table name    SET NOCOUNT ON DECLARE  AllTables table  DbName sysname SchemaName sysname  TableName sysname  DECLARE       SearchDb nvarchar 200        SearchSchema nvarchar 200        SearchTable nvarchar 200        SQL nvarchar 4000  SET  SearchDb     SET  SearchSchema     SET  SearchTable   Account   SET  SQL  select       as DbName  s name as SchemaName  t name as TableName from     sys tables t inner join sys schemas s on t schema id s schema id WHERE       LIKE      SearchDb     AND s name LIKE      SearchSchema     AND t name LIKE      SearchTable       INSERT INTO  AllTables  DbName  SchemaName  TableName      EXEC sp msforeachdb  SQL SET NOCOUNT OFF SELECT   FROM  AllTables ORDER BY DbName  SchemaName  TableName

User · Answer

please fill the  likeTablename param for search table   now this parameter set to  tbltrans  for search all table contain tbltrans in name   set  likeTablename to     to show all table   declare  AllTableNames nvarchar max    select   AllTableNames STUFF  select   SELECT  TABLE CATALOG collate DATABASE DEFAULT       TABLE SCHEMA collate DATABASE DEFAULT       TABLE NAME collate DATABASE DEFAULT as tablename FROM   name   INFORMATION SCHEMA TABLES WHERE TABLE TYPE     BASE TABLE   union    FROM master sys databases  FOR XML PATH      TYPE   value       NVARCHAR MAX      1 1       set  AllTableNames left  AllTableNames len  AllTableNames -6   declare  likeTablename nvarchar 200    tbltrans    set  AllTableNames N select tablename from    AllTableNames N  at where tablename like     N     likeTablename N    N     exec sp executesql   AllTableNames

User · Answer

Here s a tutorial providing a T-SQL script that will return the following fields for each table from each database located in a SQL Server Instance    ServerName  DatabaseName  SchemaName   TableName   ColumnName   KeyType   https   tidbytez com 2015 06 01 map-the-table-structure-of-a-sql-server-database      SCRIPT UPDATED 20180316     USE  master  GO    DROP TEMP TABLES IF THEY EXIST   IF OBJECT ID  tempdb   DatabaseList   IS NOT NULL     DROP TABLE  DatabaseList   IF OBJECT ID  tempdb   TableStructure   IS NOT NULL     DROP TABLE  TableStructure   IF OBJECT ID  tempdb   ErrorTable   IS NOT NULL     DROP TABLE  ErrorTable   IF OBJECT ID  tempdb   MappedServer   IS NOT NULL     DROP TABLE  MappedServer   DECLARE  ServerName AS SYSNAME  SET  ServerName     SERVERNAME  CREATE TABLE  DatabaseList       Id INT NOT NULL IDENTITY 1  1  PRIMARY KEY      ServerName SYSNAME      DbName SYSNAME         CREATE TABLE   TableStructure         DbName  SYSNAME       SchemaName  SYSNAME       TableName  SYSNAME       ColumnName  SYSNAME       KeyType  CHAR 7        ON  PRIMARY      THE ERROR TABLE WILL STORE THE DYNAMIC SQL THAT DID NOT WORK   CREATE TABLE   ErrorTable    SqlCommand  VARCHAR MAX   ON  PRIMARY       A LIST OF DISTINCT DATABASE NAMES IS CREATED THESE TWO COLUMNS ARE STORED IN THE  DatabaseList TEMP TABLE THIS TABLE IS USED IN A FOR LOOP TO GET EACH DATABASE NAME    INSERT INTO  DatabaseList       ServerName      DbName       SELECT  ServerName      NAME AS DbName FROM master dbo sysdatabases WITH  NOLOCK  WHERE NAME  lt  gt   tempdb  ORDER BY NAME ASC    VARIABLES ARE DECLARED FOR USE IN THE FOLLOWING FOR LOOP   DECLARE  sqlCommand AS VARCHAR MAX  DECLARE  DbName AS SYSNAME DECLARE  i AS INT DECLARE  z AS INT  SET  i   1 SET  z             SELECT COUNT      1         FROM  DatabaseList              WHILE 1 IS LESS THAN THE NUMBER OF DATABASE NAMES IN  DatabaseList   WHILE  i  lt   z BEGIN       GET NEW DATABASE NAME       SET  DbName                 SELECT  DbName              FROM  DatabaseList             WHERE Id    i                     CREATE DYNAMIC SQL TO GET EACH TABLE NAME AND COLUMN NAME FROM EACH DATABASE       SET  sqlCommand    USE       DbName             INSERT INTO   TableStructure  SELECT DISTINCT            DbName            AS DbName      SCHEMA NAME SCHEMA ID  AS SchemaName      T NAME AS TableName          C NAME AS ColumnName      CASE          WHEN OBJECTPROPERTY OBJECT ID iskcu CONSTRAINT NAME     IsPrimaryKey      1              THEN   Primary            WHEN OBJECTPROPERTY OBJECT ID iskcu CONSTRAINT NAME     IsForeignKey      1              THEN   Foreign           ELSE NULL          END AS   KeyType   FROM SYS TABLES AS t WITH  NOLOCK  INNER JOIN SYS COLUMNS C ON T OBJECT ID   C OBJECT ID LEFT JOIN INFORMATION SCHEMA KEY COLUMN USAGE AS iskcu WITH  NOLOCK   ON SCHEMA NAME SCHEMA ID    iskcu TABLE SCHEMA      AND T NAME   iskcu TABLE NAME     AND C NAME   iskcu COLUMN NAME ORDER BY SchemaName ASC      TableName ASC      ColumnName ASC            ERROR HANDLING       BEGIN TRY         EXEC   sqlCommand      END TRY      BEGIN CATCH         INSERT INTO  ErrorTable         SELECT   sqlCommand      END CATCH      SET  i    i   1 END     JOIN THE TEMP TABLES TOGETHER TO CREATE A MAPPED STRUCTURE OF THE SERVER ADDITIONAL FIELDS ARE ADDED TO MAKE SELECTING TABLES AND FIELDS EASIER    SELECT DISTINCT   SERVERNAME AS ServerName      DL DbName      TS SchemaName      TS TableName      TS ColumnName      TS  KeyType             QUOTENAME TS ColumnName  AS BracketedColumn            QUOTENAME TS TableName          QUOTENAME TS ColumnName  AS BracketedTableAndColumn       SELECT   FROM     QUOTENAME DL DbName          QUOTENAME TS SchemaName          QUOTENAME TS TableName     --WHERE --GROUP BY --HAVING --ORDER BY  AS  SelectTable        SELECT     QUOTENAME TS TableName          QUOTENAME TS ColumnName      FROM     QUOTENAME DL DbName          QUOTENAME TS SchemaName          QUOTENAME TS TableName     --WHERE --GROUP BY --HAVING --ORDER BY  AS  SelectColumn  INTO  MappedServer FROM   DatabaseList  AS DL INNER JOIN   TableStructure  AS TS ON DL DbName   TS DbName ORDER BY DL DbName ASC      TS SchemaName ASC      TS TableName ASC      TS ColumnName ASC     HOUSE KEEPING    IF OBJECT ID  tempdb   DatabaseList   IS NOT NULL     DROP TABLE  DatabaseList   IF OBJECT ID  tempdb   TableStructure   IS NOT NULL     DROP TABLE  TableStructure   SELECT   FROM  ErrorTable   IF OBJECT ID  tempdb   ErrorTable   IS NOT NULL     DROP TABLE  ErrorTable      THE DATA RETURNED CAN NOW BE EXPORTED TO EXCEL USING A FILTERED SEARCH WILL NOW MAKE FINDING FIELDS A VERY EASY PROCESS    SELECT ServerName      DbName      SchemaName      TableName      ColumnName      KeyType      BracketedColumn      BracketedTableAndColumn      SelectColumn      SelectTable FROM  MappedServer ORDER BY DbName ASC      SchemaName ASC      TableName ASC      ColumnName ASC

User · Answer

I realize this is a very old thread  but it was very helpful when I had to put together some system documentation for several different servers that were hosting different versions of Sql Server  I ended up creating 4 stored procedures which I am posting here for the benefit of the community  We use Dynamics NAV so the two stored procedures with NAV in the name split the Nav company out of the table name  Enjoy     1 of 4 - ListServerDatabases  USE  YourDatabase  GO          Object   StoredProcedure  pssi   ListServerDatabases     Script Date  10 3 2017 8 56 45 AM         SET ANSI NULLS ON GO SET QUOTED IDENTIFIER ON GO  ALTER PROC  dbo   ListServerDatabases         SearchDatabases varchar max    NULL         ExcludeSystemDatabases bit   1       Sql varchar max  OUTPUT   AS BEGIN                                                                                                                                            Lists all of the databases for a given server      Parameters         SearchDatabases - Comma delimited list of database names for which to search - converted into series of Like statements                           Defaults to null           ExcludeSystemDatabases - 1 to exclude system databases  otherwise 0                                  Defaults to 1         Sql - Output - the stored proc generated sql       Adapted from answer by      From  How do I list all tables in all databases in SQL Server in a single result set      Link  https   stackoverflow com questions 2875768 how-do-i-list-all-tables-in-all-databases-in-sql-server-in-a-single-result-set                                                                                                                                                SET NOCOUNT ON      DECLARE  l CompoundLikeStatement varchar max           DECLARE  l DatabaseName sysname      DECLARE  l Index int      DECLARE  lUseAndText bit   0      DECLARE  l AllDatabases table  ServerName sysname  DbName sysname       SET  Sql             select   ServerName as   ServerName          as   DbName         IF  SearchDatabases IS NOT NULL BEGIN         SET  l CompoundLikeStatement   char 13     where      char 13          WHILE LEN LTRIM RTRIM  SearchDatabases     gt  0 BEGIN             SET  l Index   CHARINDEX       SearchDatabases              IF  l Index   0 BEGIN                 SET  l DatabaseName   LTRIM RTRIM  SearchDatabases               END ELSE BEGIN                 SET  l DatabaseName   LTRIM RTRIM LEFT  SearchDatabases   l Index - 1                END              SET  SearchDatabases   LTRIM RTRIM REPLACE LTRIM RTRIM REPLACE  SearchDatabases   l DatabaseName                                SET  l CompoundLikeStatement    l CompoundLikeStatement   char 13            like        l DatabaseName        COLLATE Latin1 General CI AS or           END          -- Trim trailing Or and add closing right parenthesis           SET  l CompoundLikeStatement   LTRIM RTRIM  l CompoundLikeStatement           SET  l CompoundLikeStatement   LEFT  l CompoundLikeStatement  LEN  l CompoundLikeStatement  - 2                  SET  Sql    Sql   char 13                 l CompoundLikeStatement          SET  lUseAndText   1     END      IF  ExcludeSystemDatabases   1 BEGIN         SET  Sql    Sql   char 13          SET  Sql    Sql   case when  lUseAndText   1 THEN    and   ELSE  where   END                      not in    master   COLLATE Latin1 General CI AS    model   COLLATE Latin1 General CI AS    msdb   COLLATE Latin1 General CI AS    tempdb   COLLATE Latin1 General CI AS        END      PRINT  Sql          INSERT INTO  l AllDatabases      EXEC sp msforeachdb  Sql      SELECT   FROM  l AllDatabases ORDER BY DbName END

User · Answer

I m pretty sure you ll have to loop through the list of databases and then list each table  You should be able to union them together

User · Answer

I realize this is a very old thread  but it was very helpful when I had to put together some system documentation for several different servers that were hosting different versions of Sql Server  I ended up creating 4 stored procedures which I am posting here for the benefit of the community  We use Dynamics NAV so the two stored procedures with NAV in the name split the Nav company out of the table name  Enjoy     4 of 4 - ListServerDatabaseNavTables - for Dynamics NAV  USE  YourDatabase  GO  SET QUOTED IDENTIFIER ON GO  ALTER proc  dbo   ListServerDatabaseNavTables         SearchDatabases varchar max    NULL         SearchSchema sysname   NULL       SearchCompanies varchar max    NULL       SearchTables varchar max    NULL       ExcludeSystemDatabases bit   1       Sql varchar max  OUTPUT   AS BEGIN                                                                                                                                            Lists all of the database tables for a given server      Parameters         SearchDatabases - Comma delimited list of database names for which to search - converted into series of Like statements                           Defaults to null           SearchSchema - Schema name for which to search                        Defaults to null          SearchCompanies - Comma delimited list of company names for which to search - converted into series of Like statements                           Defaults to null           SearchTables - Comma delimited list of table names for which to search - converted into series of Like statements                        Defaults to null          ExcludeSystemDatabases - 1 to exclude system databases  otherwise 0                            Defaults to 1         Sql - Output - the stored proc generated sql       Adapted from answer by KM answered May 21  10 at 13 33     From  How do I list all tables in all databases in SQL Server in a single result set      Link  https   stackoverflow com questions 2875768 how-do-i-list-all-tables-in-all-databases-in-sql-server-in-a-single-result-set                                                                                                                                                SET NOCOUNT ON      DECLARE  l CompoundLikeStatement varchar max           DECLARE  l TableName sysname     DECLARE  l CompanyName sysname     DECLARE  l DatabaseName sysname      DECLARE  l Index int      DECLARE  l UseAndText bit   0      DECLARE  AllTables table  ServerName sysname  DbName sysname  SchemaName sysname  CompanyName sysname  TableName sysname  NavTableName sysname       SET  Sql             select   ServerName as   ServerName          as   DbName    s name as   SchemaName        char 13                    case when charindex        t name    0 then      else left t name  charindex        t name  - 1  end as   CompanyName        char 13                    case when charindex        t name    0 then t name else substring t name  charindex        t name    1  1000  end as   TableName        char 13                    t name as   NavTableName       char 13             from     sys tables t inner join     char 13                   sys schemas s on t schema id   s schema id        -- Comma delimited list of database names for which to search     IF  SearchDatabases IS NOT NULL BEGIN         SET  l CompoundLikeStatement   char 13     where      char 13          WHILE LEN LTRIM RTRIM  SearchDatabases     gt  0 BEGIN             SET  l Index   CHARINDEX       SearchDatabases              IF  l Index   0 BEGIN                 SET  l DatabaseName   LTRIM RTRIM  SearchDatabases               END ELSE BEGIN                 SET  l DatabaseName   LTRIM RTRIM LEFT  SearchDatabases   l Index - 1                END              SET  SearchDatabases   LTRIM RTRIM REPLACE LTRIM RTRIM REPLACE  SearchDatabases   l DatabaseName                                SET  l CompoundLikeStatement    l CompoundLikeStatement   char 13            like        l DatabaseName        COLLATE Latin1 General CI AS or           END          -- Trim trailing Or and add closing right parenthesis           SET  l CompoundLikeStatement   LTRIM RTRIM  l CompoundLikeStatement           SET  l CompoundLikeStatement   LEFT  l CompoundLikeStatement  LEN  l CompoundLikeStatement  - 2                 SET  Sql    Sql   char 13                 l CompoundLikeStatement          SET  l UseAndText   1     END      -- Search schema     IF  SearchSchema IS NOT NULL BEGIN         SET  Sql    Sql   char 13          SET  Sql    Sql   CASE WHEN  l UseAndText   1 THEN    and   ELSE  where   END                s name LIKE        SearchSchema       COLLATE Latin1 General CI AS          SET  l UseAndText   1     END      -- Comma delimited list of company names for which to search     IF  SearchCompanies IS NOT NULL BEGIN         SET  l CompoundLikeStatement   char 13    CASE WHEN  l UseAndText   1 THEN    and    ELSE  where    END   char 13           WHILE LEN LTRIM RTRIM  SearchCompanies     gt  0 BEGIN             SET  l Index   CHARINDEX       SearchCompanies              IF  l Index   0 BEGIN                 SET  l CompanyName   LTRIM RTRIM  SearchCompanies               END ELSE BEGIN                 SET  l CompanyName   LTRIM RTRIM LEFT  SearchCompanies   l Index - 1                END              SET  SearchCompanies   LTRIM RTRIM REPLACE LTRIM RTRIM REPLACE  SearchCompanies   l CompanyName                                SET  l CompoundLikeStatement    l CompoundLikeStatement   char 13      t name like        l CompanyName        COLLATE Latin1 General CI AS or           END          -- Trim trailing Or and add closing right parenthesis           SET  l CompoundLikeStatement   LTRIM RTRIM  l CompoundLikeStatement           SET  l CompoundLikeStatement   LEFT  l CompoundLikeStatement  LEN  l CompoundLikeStatement  - 2                  SET  Sql    Sql   char 13                 l CompoundLikeStatement          SET  l UseAndText   1     END      -- Comma delimited list of table names for which to search     IF  SearchTables IS NOT NULL BEGIN         SET  l CompoundLikeStatement   char 13    CASE WHEN  l UseAndText   1 THEN    and    ELSE  where    END   char 13           WHILE LEN LTRIM RTRIM  SearchTables     gt  0 BEGIN             SET  l Index   CHARINDEX       SearchTables              IF  l Index   0 BEGIN                 SET  l TableName   LTRIM RTRIM  SearchTables               END ELSE BEGIN                 SET  l TableName   LTRIM RTRIM LEFT  SearchTables   l Index - 1                END              SET  SearchTables   LTRIM RTRIM REPLACE LTRIM RTRIM REPLACE  SearchTables   l TableName                                SET  l CompoundLikeStatement    l CompoundLikeStatement   char 13      t name like         l TableName       COLLATE Latin1 General CI AS or           END          -- Trim trailing Or and add closing right parenthesis           SET  l CompoundLikeStatement   LTRIM RTRIM  l CompoundLikeStatement           SET  l CompoundLikeStatement   LEFT  l CompoundLikeStatement  LEN  l CompoundLikeStatement  - 2                  SET  Sql    Sql   char 13                 l CompoundLikeStatement          SET  l UseAndText   1     END      IF  ExcludeSystemDatabases   1 BEGIN         SET  Sql    Sql   char 13          SET  Sql    Sql   case when  l UseAndText   1 THEN    and   ELSE  where   END                      not in    master   COLLATE Latin1 General CI AS    model   COLLATE Latin1 General CI AS    msdb   COLLATE Latin1 General CI AS    tempdb   COLLATE Latin1 General CI AS        END      PRINT  Sql          INSERT INTO  AllTables      EXEC sp msforeachdb  Sql      SELECT   FROM  AllTables ORDER BY DbName COLLATE Latin1 General CI AS  CompanyName COLLATE Latin1 General CI AS  TableName COLLATE Latin1 General CI AS END

User · Answer

All you need to do is run the sp tables stored procedure  http   msdn microsoft com en-us library aa260318 SQL 80  aspx

User · Answer

I realize this is a very old thread  but it was very helpful when I had to put together some system documentation for several different servers that were hosting different versions of Sql Server  I ended up creating 4 stored procedures which I am posting here for the benefit of the community  We use Dynamics NAV so the two stored procedures with NAV in the name split the Nav company out of the table name  Enjoy     2 of 4 - ListServerDatabaseTables  USE  YourDatabase  GO  SET QUOTED IDENTIFIER ON GO  ALTER PROC  dbo   ListServerDatabaseTables         SearchDatabases varchar max    NULL         SearchSchema sysname   NULL       SearchTables varchar max    NULL       ExcludeSystemDatabases bit   1       Sql varchar max  OUTPUT   AS BEGIN                                                                                                                                            Lists all of the database tables for a given server      Parameters         SearchDatabases - Comma delimited list of database names for which to search - converted into series of Like statements                           Defaults to null           SearchSchema - Schema name for which to search                        Defaults to null          SearchTables - Comma delimited list of table names for which to search - converted into series of Like statements                        Defaults to null          ExcludeSystemDatabases - 1 to exclude system databases  otherwise 0                            Defaults to 1         Sql - Output - the stored proc generated sql       Adapted from answer by KM answered May 21  10 at 13 33     From  How do I list all tables in all databases in SQL Server in a single result set      Link  https   stackoverflow com questions 2875768 how-do-i-list-all-tables-in-all-databases-in-sql-server-in-a-single-result-set                                                                                                                                                SET NOCOUNT ON      DECLARE  l CompoundLikeStatement varchar max           DECLARE  l TableName sysname     DECLARE  l DatabaseName sysname      DECLARE  l Index int      DECLARE  l UseAndText bit   0      DECLARE  AllTables table  ServerName sysname  DbName sysname  SchemaName sysname  TableName sysname       SET  Sql             select   ServerName as   ServerName          as   DbName    s name as   SchemaName    t name as   TableName       char 13             from     sys tables t inner join     char 13                   sys schemas s on t schema id   s schema id        -- Comma delimited list of database names for which to search     IF  SearchDatabases IS NOT NULL BEGIN         SET  l CompoundLikeStatement   char 13     where      char 13          WHILE LEN LTRIM RTRIM  SearchDatabases     gt  0 BEGIN             SET  l Index   CHARINDEX       SearchDatabases              IF  l Index   0 BEGIN                 SET  l DatabaseName   LTRIM RTRIM  SearchDatabases               END ELSE BEGIN                 SET  l DatabaseName   LTRIM RTRIM LEFT  SearchDatabases   l Index - 1                END              SET  SearchDatabases   LTRIM RTRIM REPLACE LTRIM RTRIM REPLACE  SearchDatabases   l DatabaseName                                SET  l CompoundLikeStatement    l CompoundLikeStatement   char 13            like        l DatabaseName        COLLATE Latin1 General CI AS or           END          -- Trim trailing Or and add closing right parenthesis           SET  l CompoundLikeStatement   LTRIM RTRIM  l CompoundLikeStatement           SET  l CompoundLikeStatement   LEFT  l CompoundLikeStatement  LEN  l CompoundLikeStatement  - 2                 SET  Sql    Sql   char 13                 l CompoundLikeStatement          SET  l UseAndText   1     END      -- Search schema     IF  SearchSchema IS NOT NULL BEGIN         SET  Sql    Sql   char 13          SET  Sql    Sql   CASE WHEN  l UseAndText   1 THEN    and   ELSE  where   END                s name LIKE        SearchSchema       COLLATE Latin1 General CI AS          SET  l UseAndText   1     END      -- Comma delimited list of table names for which to search     IF  SearchTables IS NOT NULL BEGIN         SET  l CompoundLikeStatement   char 13    CASE WHEN  l UseAndText   1 THEN    and    ELSE  where    END   char 13           WHILE LEN LTRIM RTRIM  SearchTables     gt  0 BEGIN             SET  l Index   CHARINDEX       SearchTables              IF  l Index   0 BEGIN                 SET  l TableName   LTRIM RTRIM  SearchTables               END ELSE BEGIN                 SET  l TableName   LTRIM RTRIM LEFT  SearchTables   l Index - 1                END              SET  SearchTables   LTRIM RTRIM REPLACE LTRIM RTRIM REPLACE  SearchTables   l TableName                                SET  l CompoundLikeStatement    l CompoundLikeStatement   char 13      t name like         l TableName       COLLATE Latin1 General CI AS or           END          -- Trim trailing Or and add closing right parenthesis           SET  l CompoundLikeStatement   LTRIM RTRIM  l CompoundLikeStatement           SET  l CompoundLikeStatement   LEFT  l CompoundLikeStatement  LEN  l CompoundLikeStatement  - 2                  SET  Sql    Sql   char 13                 l CompoundLikeStatement          SET  l UseAndText   1     END      IF  ExcludeSystemDatabases   1 BEGIN         SET  Sql    Sql   char 13          SET  Sql    Sql   case when  l UseAndText   1 THEN    and   ELSE  where   END                      not in    master   COLLATE Latin1 General CI AS    model   COLLATE Latin1 General CI AS    msdb   COLLATE Latin1 General CI AS    tempdb   COLLATE Latin1 General CI AS        END      PRINT  Sql          INSERT INTO  AllTables      EXEC sp msforeachdb  Sql      SELECT   FROM  AllTables ORDER BY DbName COLLATE Latin1 General CI AS  SchemaName COLLATE Latin1 General CI AS  TableName COLLATE Latin1 General CI AS END

User · Answer

I think the common approach is to SELECT   FROM INFORMATION SCHEMA TABLES for each database using sp MSforeachdb  I created a snippet in VS Code that I think it might be helpful   Query  IF OBJECT ID  tempdb   alltables    U   IS NOT NULL DROP TABLE  alltables  SELECT   INTO  alltables FROM INFORMATION SCHEMA TABLES  TRUNCATE TABLE  alltables  EXEC sp MSforeachdb  USE     INSERT INTO  alltables SELECT   from INFORMATION SCHEMA TABLES   SELECT   FROM  alltables WHERE TABLE NAME LIKE    lt TABLE NAME TO SEARCH gt     GO    Snippet         List all tables              prefix    sqlListTable            body                  IF OBJECT ID  tempdb   alltables    U   IS NOT NULL DROP TABLE  alltables                 SELECT   INTO  alltables FROM INFORMATION SCHEMA TABLES                 TRUNCATE TABLE  alltables                 EXEC sp MSforeachdb  USE     INSERT INTO  alltables SELECT   from INFORMATION SCHEMA TABLES                  SELECT   FROM  alltables WHERE TABLE NAME LIKE    0                   GO

User · Answer

This is really handy  but I wanted a way to show all user objects  not just tables  so I adapted it to use sys objects instead of sys tables  SET NOCOUNT ON DECLARE  AllTables table  DbName sysname SchemaName sysname  ObjectType char 2   ObjectName sysname  DECLARE       SearchDb nvarchar 200        SearchSchema nvarchar 200        SearchObject nvarchar 200        SQL nvarchar 4000  SET  SearchDb     SET  SearchSchema     SET  SearchObject   Something   SET  SQL  select       as DbName  s name as SchemaName  t type as ObjectType  t name as ObjectName  from     sys objects t inner join sys schemas s on t schema id s schema id  WHERE t type in    FN     IF     U     V     P     TF     AND       LIKE      SearchDb      AND s name LIKE      SearchSchema      AND t name LIKE      SearchObject       INSERT INTO  AllTables  DbName  SchemaName  ObjectType  ObjectName      EXEC sp msforeachdb  SQL SET NOCOUNT OFF SELECT   FROM  AllTables ORDER BY DbName  SchemaName  ObjectType  ObjectName

User · Answer

I needed something that I could use to search all my servers using CMS and search by server  DB  schema or table  This is what I found  originally posted by Michael Sorens here  How do I list all tables in all databases in SQL Server in a single result set      SET NOCOUNT ON DECLARE  AllTables TABLE                    ServerName NVARCHAR 200           DBName NVARCHAR 200           SchemaName NVARCHAR 200           TableName NVARCHAR 200            DECLARE  SearchSvr NVARCHAR 200           SearchDB NVARCHAR 200           SearchS NVARCHAR 200           SearchTbl NVARCHAR 200           SQL NVARCHAR 4000   SET  SearchSvr   NULL  --Search for Servers  NULL for all Servers SET  SearchDB   NULL  --Search for DB  NULL for all Databases SET  SearchS   NULL  --Search for Schemas  NULL for all Schemas SET  SearchTbl   NULL  --Search for Tables  NULL for all Tables  SET  SQL    SELECT   SERVERNAME                         s name          t name          FROM     sys tables t           JOIN sys schemas s on t schema id s schema id           WHERE   SERVERNAME LIKE        ISNULL  SearchSvr                      AND       LIKE        ISNULL  SearchDB                      AND s name LIKE        ISNULL  SearchS                      AND t name LIKE        ISNULL  SearchTbl                   -- AND       NOT IN    master     model     msdb     tempdb     SSISDB                 -- Remove the  --  from the last statement in the WHERE clause to exclude system tables  INSERT  INTO  AllTables                    ServerName          DBName          SchemaName          TableName                   EXEC sp MSforeachdb  SQL SET NOCOUNT OFF SELECT    FROM     AllTables ORDER BY 1 2 3 4

User · Answer

I posted an answer a while back here that you could use here  The outline is    Create a temp table Call sp msForEachDb The query run against each DB stores the data in the temp table When done  query the temp table

User · Answer

Link to a stored-procedure-less approach that Bart Gawrych posted on Dataedo site  I was asking myself   Do we really have to use a stored procedure here   and I found this helpful post   The state 0 was added to fix issues with offline databases per feedback from users of the linked page    declare  sql nvarchar max    select  sql         select   UNION ALL         SELECT        quotename name           as database name                 s name COLLATE DATABASE DEFAULT                     AS schema name                 t name COLLATE DATABASE DEFAULT as table name                 FROM    quotename name      sys tables t                JOIN    quotename name      sys schemas s                     on s schema id   t schema id      from sys databases      where state 0     order by  name  for xml path      type  value       nvarchar max      set  sql   stuff  sql  1  12          order by database name                                                  schema name                                                 table name    execute   sql

User · Answer

for a simple way to get all tables on the server  try this   SET NOCOUNT ON DECLARE  AllTables table  CompleteTableName nvarchar 4000   INSERT INTO  AllTables  CompleteTableName      EXEC sp msforeachdb  select   SERVERNAME                   s name       t name from     sys tables t inner join sys schemas s on t schema id s schema id  SET NOCOUNT OFF SELECT   FROM  AllTables ORDER BY 1   it will return a single column that contains the server database schema table name  sample output   CompleteTableName -------------------------------------------- YourServer YourDatabase1 YourSchema1 YourTable1 YourServer YourDatabase1 YourSchema1 YourTable2 YourServer YourDatabase1 YourSchema2 YourTable1 YourServer YourDatabase1 YourSchema2 YourTable2 YourServer YourDatabase2 YourSchema1 YourTable1   if you are not on SQL Server 2005 or up  replace the DECLARE  AllTables table with CREATE TABLE  AllTables and then every  AllTables with  AllTables and it will work   EDIT here is a version that will allow a search parameter to be used on any part or parts of the server database schema table names   SET NOCOUNT ON DECLARE  AllTables table  CompleteTableName nvarchar 4000   DECLARE  Search nvarchar 4000           SQL   nvarchar 4000  SET  Search null --all rows SET  SQL  select   SERVERNAME                   s name       t name from     sys tables t inner join sys schemas s on t schema id s schema id WHERE   SERVERNAME                   s name       t name LIKE      ISNULL  SEARCH            INSERT INTO  AllTables  CompleteTableName      EXEC sp msforeachdb  SQL SET NOCOUNT OFF SELECT   FROM  AllTables ORDER BY 1   set  Search to NULL for all tables  set it to things like  dbo users  or  users  or   master dbo  or even include wildcards like   master   u   etc

User · Answer

I quite like using INFORMATION SCHEMA for this as I get the DB name for free   That and - realising from  KM post that multiple results sets insert nicely - I came up with   select top 0        into  temp     from INFORMATION SCHEMA TABLES  insert into  temp     exec sp msforeachdb  select   from     INFORMATION SCHEMA TABLES   select   from  temp  drop table  temp

User · Answer

declare  sql nvarchar max   set  sql   N select cast   master   as sysname  as db name  name collate Latin1 General CI AI  object id  schema id  cast 1 as int  as database id  from master sys tables     select  sql    sql   N  union all select     quotename name           name collate Latin1 General CI AI  object id  schema id      cast database id as nvarchar 10     N  from     quotename name    N  sys tables  from sys databases where database id  gt  1 and state   0 and user access   0   exec sp executesql  sql

User · Answer

I realize this is a very old thread  but it was very helpful when I had to put together some system documentation for several different servers that were hosting different versions of Sql Server  I ended up creating 4 stored procedures which I am posting here for the benefit of the community  We use Dynamics NAV so the two stored procedures with NAV in the name split the Nav company out of the table name  Enjoy     3 of 4 - ListServerDatabaseNavCompanies - for Dynamics NAV  USE  YourDatabase  GO  SET QUOTED IDENTIFIER ON GO  ALTER PROC  dbo   ListServerDatabaseNavCompanies         SearchDatabases varchar max    NULL         SearchSchema sysname   NULL       SearchCompanies varchar max    NULL       OrderByDatabaseNameFirst bit   1        ExcludeSystemDatabases bit   1        Sql varchar max  OUTPUT   AS BEGIN                                                                                                                                            Lists all of the database companies for a given server      Parameters         SearchDatabases - Comma delimited list of database names for which to search - converted into series of Like statements                           Defaults to null           SearchSchema - Schema name for which to search                        Defaults to null          SearchCompanies - Comma delimited list of company names for which to search - converted into series of Like statements                           Defaults to null           OrderByDatabaseNameFirst - 1 to sort by Database name and then Company Name  otherwise 0 to sort by Company name first                                     Defaults to 1         ExcludeSystemDatabases - 1 to exclude system databases  otherwise 0                            Defaults to 1         Sql - Output - the stored proc generated sql       Adapted from answer by KM answered May 21  10 at 13 33     From  How do I list all tables in all databases in SQL Server in a single result set      Link  https   stackoverflow com questions 2875768 how-do-i-list-all-tables-in-all-databases-in-sql-server-in-a-single-result-set                                                                                                                                                SET NOCOUNT ON      DECLARE  l CompoundLikeStatement varchar max           DECLARE  l CompanyName sysname     DECLARE  l DatabaseName sysname      DECLARE  l Index int      DECLARE  l UseAndText bit   0      DECLARE  l Companies table  ServerName sysname  DbName sysname  SchemaName sysname  CompanyName sysname       SET  Sql             select distinct   ServerName as   ServerName          as   DbName    s name as   SchemaName        char 13                     case when charindex        t name    0 then      else left t name  charindex        t name  - 1  end as   CompanyName      char 13             from     sys tables t inner join     char 13                   sys schemas s on t schema id   s schema id        -- Comma delimited list of database names for which to search     IF  SearchDatabases IS NOT NULL BEGIN         SET  l CompoundLikeStatement   char 13     where      char 13          WHILE LEN LTRIM RTRIM  SearchDatabases     gt  0 BEGIN             SET  l Index   CHARINDEX       SearchDatabases              IF  l Index   0 BEGIN                 SET  l DatabaseName   LTRIM RTRIM  SearchDatabases               END ELSE BEGIN                 SET  l DatabaseName   LTRIM RTRIM LEFT  SearchDatabases   l Index - 1                END              SET  SearchDatabases   LTRIM RTRIM REPLACE LTRIM RTRIM REPLACE  SearchDatabases   l DatabaseName                                SET  l CompoundLikeStatement    l CompoundLikeStatement   char 13            like        l DatabaseName        COLLATE Latin1 General CI AS or           END          -- Trim trailing Or and add closing right parenthesis           SET  l CompoundLikeStatement   LTRIM RTRIM  l CompoundLikeStatement           SET  l CompoundLikeStatement   LEFT  l CompoundLikeStatement  LEN  l CompoundLikeStatement  - 2                 SET  Sql    Sql   char 13                 l CompoundLikeStatement          SET  l UseAndText   1     END      -- Search schema     IF  SearchSchema IS NOT NULL BEGIN         SET  Sql    Sql   char 13          SET  Sql    Sql   CASE WHEN  l UseAndText   1 THEN    and   ELSE  where   END                s name LIKE        SearchSchema       COLLATE Latin1 General CI AS          SET  l UseAndText   1     END      -- Comma delimited list of company names for which to search     IF  SearchCompanies IS NOT NULL BEGIN         SET  l CompoundLikeStatement   char 13    CASE WHEN  l UseAndText   1 THEN    and    ELSE  where    END   char 13           WHILE LEN LTRIM RTRIM  SearchCompanies     gt  0 BEGIN             SET  l Index   CHARINDEX       SearchCompanies              IF  l Index   0 BEGIN                 SET  l CompanyName   LTRIM RTRIM  SearchCompanies               END ELSE BEGIN                 SET  l CompanyName   LTRIM RTRIM LEFT  SearchCompanies   l Index - 1                END              SET  SearchCompanies   LTRIM RTRIM REPLACE LTRIM RTRIM REPLACE  SearchCompanies   l CompanyName                                SET  l CompoundLikeStatement    l CompoundLikeStatement   char 13      t name like        l CompanyName        COLLATE Latin1 General CI AS or           END          -- Trim trailing Or and add closing right parenthesis           SET  l CompoundLikeStatement   LTRIM RTRIM  l CompoundLikeStatement           SET  l CompoundLikeStatement   LEFT  l CompoundLikeStatement  LEN  l CompoundLikeStatement  - 2                  SET  Sql    Sql   char 13                 l CompoundLikeStatement          SET  l UseAndText   1     END      IF  ExcludeSystemDatabases   1 BEGIN         SET  Sql    Sql   char 13          SET  Sql    Sql   case when  l UseAndText   1 THEN    and   ELSE  where   END                      not in    master   COLLATE Latin1 General CI AS    model   COLLATE Latin1 General CI AS    msdb   COLLATE Latin1 General CI AS    tempdb   COLLATE Latin1 General CI AS        END         PRINT  Sql         INSERT INTO  l Companies      EXEC sp msforeachdb  Sql      SELECT CASE WHEN  OrderByDatabaseNameFirst   1 THEN  DbName  amp  CompanyName  ELSE  CompanyName  amp  DbName  END AS  Sorted by      SELECT ServerName  DbName COLLATE Latin1 General CI AS AS  DbName   SchemaName COLLATE Latin1 General CI AS AS  SchemaName   CompanyName COLLATE Latin1 General CI AS AS  CompanyName      FROM  l Companies      ORDER BY SchemaName COLLATE Latin1 General CI AS          CASE WHEN  OrderByDatabaseNameFirst   1 THEN DbName COLLATE Latin1 General CI AS ELSE CompanyName COLLATE Latin1 General CI AS END          CASE WHEN  OrderByDatabaseNameFirst   1 THEN CompanyName COLLATE Latin1 General CI AS ELSE DbName COLLATE Latin1 General CI AS END END

[sql-server] How do I list all tables in all databases in SQL Server in a single result set?

Examples related to sql-server

Examples related to metadata