选择 sysobjects 中具有名为“mytable”表的所有数据库；与架构无关？

Question

对于给定的 sql 2000 - 2008 服务器，我想在该服务器上找到任何名为 dbo.[MyTable] 的表。此外，如何查找具有名为 [dbo].[MyTable] 和 [AnySchemaName].[MyTable] 表的所有数据库。有没有像 spTables MyTable 这样简单的 sp_ 命令？或者“sp_AllDatabaseTable [MyTable]”？

我想将其打印出来，例如：

ServerName  Database  SchemaName  MyTable   Date Created
----------- --------- ----------- --------- -------------

Thx

Answer 1

我真的更喜欢一个既不使用 CURSORS 也不使用 sp_msforeachdb 的解决方案。

下面的解决方案为您提供了一个想法，您可以根据自己的需要进行调整。

USE [master]
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


BEGIN TRY



SET NOCOUNT ON
SET DATEFORMAT DMY
SET DEADLOCK_PRIORITY NORMAL;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE  @log NVARCHAR(MAX)
        ,@vCrlf CHAR(2);

SELECT  @log = ''
       ,@vCrlf = CHAR(13)+CHAR(10);


DECLARE @SQL NVARCHAR(MAX)

BEGIN TRY DROP TABLE #OBJECTS END TRY BEGIN CATCH END CATCH
CREATE TABLE #OBJECTS(
DB_ID INT,
OBJECT_ID INT,
S_NAME SYSNAME,
NAME SYSNAME,
ROW_COUNT INT,
STATISTICS_UPDATED DATETIME)




SELECT @SQL = '

SELECT db_id=db_id(),
       o.object_id,
       s_name=s.name,
       o.name,
       ddps.row_count 
       ,[Statistics_Updated]=STATS_DATE(I.OBJECT_ID,I.INDEX_ID)
FROM sys.indexes AS i
  INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID
  INNER JOIN sys.schemas s ON s.schema_id = o.schema_id
  INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID
  AND i.index_id = ddps.index_id 
WHERE i.index_id < 2  
 AND o.is_ms_shipped = 0

 '


set @SQL = (
            SELECT STUFF(
  (SELECT N'  ' + ' USE ' + QUOTENAME(name) +';' + @vCrlf + @SQL + @vCrlf    
                                FROM SYS.DATABASES SD
                                WHERE SD.STATE_DESC = 'ONLINE' -->Skips the database if it is not online
                                  AND SD.COMPATIBILITY_LEVEL > 80  
                                  AND SD.database_id > 3 -- NO MASTER NOR TEMPDB NOR MODEL
                        FOR XML PATH(''),TYPE)
  .value('text()[1]','nvarchar(max)'),1,2,N'')
)






INSERT INTO #OBJECTS
(   [db_id],
    [object_id],
    [s_name],
    [name],
    [row_count],
    [Statistics_Updated]
)
EXECUTE MASTER.DBO.sp_executesql @SQL

SELECT * FROM #OBJECTS
--WHERE NAME = 'THE NAME THAT I AM LOOKING FOR'

END TRY
BEGIN CATCH

        PRINT '--EXCEPTION WAS CAUGHT--' + CHAR(13) +
              'THE ERROR NUMBER:' + COALESCE(CAST ( ERROR_NUMBER()  AS VARCHAR), 'NO INFO') + CHAR(13) 

        PRINT 'SEVERITY: '        + COALESCE(CAST ( ERROR_SEVERITY()  AS VARCHAR), 'NO INFO') + CHAR(13) +
              'STATE: '           + COALESCE(CAST ( ERROR_STATE() AS VARCHAR), 'NO INFO')  + CHAR(13) 

        PRINT 'PROCEDURE: '       + COALESCE(CAST ( COALESCE(ERROR_PROCEDURE(),'NO INFO')  AS VARCHAR), 'NO INFO') + CHAR(13) +
              'LINE NUMBER: '     + COALESCE(CAST ( ERROR_LINE() AS VARCHAR), 'NO INFO')  + CHAR(13) 

        PRINT 'ERROR MESSAGE: '
        PRINT  CAST ( COALESCE(ERROR_MESSAGE(),'NO INFO')   AS NTEXT)

END CATCH;

Answer 2

我更喜欢基于集合的方法：

Declare @TableName as Varchar(255)
Set @TableName = '<MyTableName>'

Declare @SQL as Varchar(max)
Select @SQL = Coalesce(@SQL + '
', '') + 
CASE 
WHEN Row_Number() Over (Order by Database_ID) = 1 
THEN '' 
ELSE 
'UNION ALL ' 
END +
'SELECT 
  ''' + d.Name + '''    as DatabaseName
, s.Name                as SchemaName 
, o.Name                as TableName 
FROM ' + d.Name +'.Sys.Objects o 
INNER JOIN ' + d.Name + '.Sys.Schemas s
ON o.Schema_ID = s.Schema_ID
WHERE o.Name like ''' + @TableName + ''''
FROM sys.databases d
where d.Name not like 'ReportServer%'
and d.Name not like 'SSISPackageRegistry'

Print @SQL 
EXEC(@SQL)

Answer 3

当然，您可以使用 sp_msforeachdb 来实现此目的，但您需要记住，fhis 函数既没有文档记录，也没有官方支持。而且，有时它会破裂。有关更多信息，请参见这里 制作更可靠、更灵活的 sp_MSforeachdb

您可以使用此脚本在所有数据库中按名称搜索表。
我从 在 SQL Server 的每个数据库中查找表

DECLARE @TableName VARCHAR(256)
SET @TableName='YOUR_TABLE_NAME'
DECLARE @DBName VARCHAR(256)
DECLARE @varSQL VARCHAR(512)
DECLARE @getDBName CURSOR
SET @getDBName = CURSOR FOR
SELECT name
FROM sys.databases
CREATE TABLE #TmpTable (DBName VARCHAR(256),
SchemaName VARCHAR(256),
TableName VARCHAR(256),
create_date date, modify_date date)
OPEN @getDBName
FETCH NEXT
FROM @getDBName INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @varSQL = 'USE ' + @DBName + ';
INSERT INTO #TmpTable
SELECT '''+ @DBName + ''' AS DBName,
SCHEMA_NAME(schema_id) AS SchemaName,
name AS TableName,
create_date, modify_date
FROM sys.tables
WHERE name LIKE ''%' + @TableName + '%''' --WHERE name = '' + @TableName + ''' /* if you want to search by exact table name*/ 
EXEC (@varSQL)
FETCH NEXT
FROM @getDBName INTO @DBName
END
CLOSE @getDBName
DEALLOCATE @getDBName
SELECT *
FROM #TmpTable
DROP TABLE #TmpTable

另外，您可能想阅读此在所有数据库的所有对象中查找表名

Answer 4

我本来会说

sp_msforeachdb 'Select * from Sysobjects where name=''MyTable'''

但你不需要这样做，sysobjects 位于主表中并且无论如何都会对所有数据库执行此操作。

您应该能够轻松找到其他列。