如何使用T-SQL列出SQL Server中的所有表名？

Question

SELECT name FROM sys.databases -- this can list all database name in the server 


user database 
SELECT * FROM INFORMATION_SCHEMA.TABLES 
    -- these two line can list the table for one particular database 

但是我怎样才能输出结果如下？

Database    Table 
---------    ------------- 
db1     t1 
db1     t2 
db2     t1 
...     ... 

Answer 1

sp_msforeachdb 'select "?" AS db, * from [?].sys.tables' 

Answer 2

，如果你需要一个结果集，试试这个：

DECLARE @AllTables table (DatabaseName sysname, TableName sysname) 
DECLARE @Current sysname 
     ,@SQL1 varchar(500) 
     ,@SQL2 varchar(500) 
     ,@SQL3 varchar(500) 
     ,@SQL  varchar(1500) 
SELECT TOP 1 @Current=Name FROM sys.databases Order By Name 
SET @SQL1='select db.name, t.TABLE_NAME from ' 
SET @SQL2='.sys.databases db inner join ' 
SET @SQL3='.INFORMATION_SCHEMA.TABLES t on db.name = t.TABLE_CATALOG' 
WHILE @Current IS NOT NULL 
BEGIN 
    SET @[email protected][email protected][email protected][email protected][email protected] 
    INSERT INTO @AllTables 
     EXEC (@SQL) 
    SELECT TOP 1 @Current=Name FROM sys.databases WHERE Name>@Current Order By Name 
    IF @@ROWCOUNT=0 BREAK 
END 

SELECT * FROM @AllTables ORDER BY DatabaseName,TableName 

Answer 3

下面是一个存储过程，我用不断地列出了数据库中使用它们的空间下令所有我的表。

GO 

    /****** Object: StoredProcedure [dbo].[dba_SpaceUsed] Script Date: 03/16/2010 15:09:55 ******/ 
    SET ANSI_NULLS ON 
    GO 
    SET QUOTED_IDENTIFIER ON 
    GO 
    CREATE PROC [dbo].[dba_SpaceUsed] 

     @SourceDB varchar (128) = null -- Optional database name 
      -- If omitted, the current database is reported. 
     , @SortBy char(1) = 'S' -- N for name, S for Size 
       -- T for table name 

    AS 

SET NOCOUNT ON 

DECLARE @sql nvarchar (4000) 

IF @SourceDB IS NULL BEGIN 
    SET @SourceDB = DB_NAME() -- The current DB 
END 

-------------------------------------------------------- 
-- Create and fill a list of the tables in the database. 

CREATE TABLE #Tables ( [schema] sysname 
         , TabName sysname) 

SELECT @sql = 'insert #tables ([schema], [TabName]) 
        select TABLE_SCHEMA, TABLE_NAME 
        from ['+ @SourceDB +'].INFORMATION_SCHEMA.TABLES 
         where TABLE_TYPE = ''BASE TABLE''' 
EXEC (@sql) 


--------------------------------------------------------------- 
-- #TabSpaceTxt Holds the results of sp_spaceused. 
-- It Doesn't have Schema Info! 
CREATE TABLE #TabSpaceTxt (
         TabName sysname 
         , [Rows] varchar (11) 
         , Reserved varchar (18) 
         , Data varchar (18) 
         , Index_Size varchar (18) 
         , Unused varchar (18) 
         ) 

--------------------------------------------------------------- 
-- The result table, with numeric results and Schema name. 
CREATE TABLE #TabSpace ([Schema] sysname 
         , TabName sysname 
         , [Rows] bigint 
         , ReservedMB numeric(18,3) 
         , DataMB numeric(18,3) 
         , Index_SizeMB numeric(18,3) 
         , UnusedMB numeric(18,3) 
         ) 

DECLARE @Tab sysname -- table name 
     , @Sch sysname -- owner,schema 

DECLARE TableCursor CURSOR FOR 
    SELECT [SCHEMA], TabNAME 
     FROM #tables 

OPEN TableCursor; 
FETCH TableCursor into @Sch, @Tab; 

WHILE @@FETCH_STATUS = 0 BEGIN 

    SELECT @sql = 'exec [' + @SourceDB 
     + ']..sp_executesql N''insert #TabSpaceTxt exec sp_spaceused ' 
     + '''''[' + @Sch + '].[' + @Tab + ']' + ''''''''; 

    Delete from #TabSpaceTxt; -- Stores 1 result at a time 
    EXEC (@sql); 

    INSERT INTO #TabSpace 
    SELECT @Sch 
     , [TabName] 
     , convert(bigint, rows) 
     , convert(numeric(18,3), convert(numeric(18,3), 
       left(reserved, len(reserved)-3))/1024.0) 
       ReservedMB 
     , convert(numeric(18,3), convert(numeric(18,3), 
       left(data, len(data)-3))/1024.0) DataMB 
     , convert(numeric(18,3), convert(numeric(18,3), 
       left(index_size, len(index_size)-3))/1024.0) 
       Index_SizeMB 
     , convert(numeric(18,3), convert(numeric(18,3), 
       left(unused, len([Unused])-3))/1024.0) 
       [UnusedMB] 
     FROM #TabSpaceTxt; 

    FETCH TableCursor into @Sch, @Tab; 
END; 

CLOSE TableCursor; 
DEALLOCATE TableCursor; 

----------------------------------------------------- 
-- Caller specifies sort, Default is size 
IF @SortBy = 'N' -- Use Schema then Table Name 
    SELECT * FROM #TabSpace 
     ORDER BY [Schema] asc, [TabName] asc 
ELSE IF @SortBy = 'T' -- Table name, then schema 
    SELECT * FROM #TabSpace 
     ORDER BY [TabName] asc, [Schema] asc 
ELSE -- S, NULL, or whatever get's the default 
    SELECT * FROM #TabSpace 
     ORDER BY ReservedMB desc 
; 

DROP TABLE #Tables 
DROP TABLE #TabSpaceTxt 
DROP TABLE #TabSpace 

--Thanks安德鲁·诺维克

Answer 4

如果你足够幸运，仍然在使用SQL2000：

CREATE TABLE #AllTables (DatabaseName sysname, TableName sysname) 
DECLARE @Current sysname 
     ,@SQL1 varchar(500) 
     ,@SQL2 varchar(500) 
     ,@SQL3 varchar(500) 
     ,@SQL  varchar(1500) 
SELECT TOP 1 @Current=Name FROM sysdatabases Order By Name 
SET @SQL1='select db.name, t.TABLE_NAME from ' 
SET @SQL2='sysdatabases db inner join ' 
SET @SQL3='.INFORMATION_SCHEMA.TABLES t on db.name = t.TABLE_CATALOG' 
WHILE @Current IS NOT NULL 
BEGIN 
    SET @[email protected][email protected][email protected][email protected] 
    --PRINT @SQL 
    SET NOCOUNT ON 
    INSERT INTO #AllTables 
     EXEC (@SQL) 
    SET NOCOUNT OFF 
    SELECT TOP 1 @Current=Name FROM sysdatabases WHERE Name>@Current Order By Name 
    IF @@ROWCOUNT=0 BREAK 
END 

SELECT * FROM #AllTables 
--where TableName = 'at_errorlog' 
ORDER BY DatabaseName,TableName 

DROP TABLE #AllTables 

Answer 5

select table_name from user_tables;