选择 sysobjects 中具有名为“mytable"的表的所有数据库，而不管模式如何? [英] select all databases in sysobjects that have a table named 'mytable' regardless of schema?

问题描述

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

For a given sql 2000 - 2008 server I want to find any table named dbo.[MyTable] on that server. In addition, how do I find all databases that have a table named [dbo].[MyTable] and [AnySchemaName].[MyTable]. Is there a simple sp_ command like spTables MyTable? Or 'sp_AllDatabaseTable [MyTable]'?

我想把它打印出来:

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

谢谢

推荐答案

当然，您可以使用 sp_msforeachdb 用于此目的，但您需要记住 fhis 函数既没有文档说明也没有官方支持.此外，有时它会破裂.更多关于这里 制作更可靠和灵活的sp_MSforeachdb

Of course, you can use sp_msforeachdb for this purpose but you need to remember that fhis function is neither documented nor officially supported. Also, sometimes it breaks. More about it here Making a more reliable and flexible sp_MSforeachdb

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

You can use this script to search table by name in all databases. I took it from Find table in every database of 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

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

Also, you may want to read this Find table name in all objects of all databases

这篇关于选择 sysobjects 中具有名为“mytable"的表的所有数据库，而不管模式如何?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！