在服务器上列出所有带有模式的表不是问题。
SELECT SCHEMA_NAME(schema_id), name FROM sys.tables
我该如何确定表所在的数据库?
在服务器上列出所有带有模式的表不是问题。
SELECT SCHEMA_NAME(schema_id), name FROM sys.tables
sys.tables存在于所有数据库中,所以我不明白你不知道自己在哪个数据库中的事实。你可以运行DB_NAME(DB_ID())来获取数据库名称。
SELECT DB_NAME(DB_ID()),SCHEMA_NAME(schema_id), name FROM sys.tables
但是在这种情况下,DB_NAME(DB_ID())将为每一行返回相同的值。
要对所有数据库执行此操作,可以这样做
EXEC sp_msforeachdb 'use [?] SELECT ''?'',SCHEMA_NAME(schema_id), name
FROM sys.tables'
当然,您也可以将它倒入表格中
CREATE TABLE #output (DatabaseName VARCHAR(1000),
SchemaName VARCHAR(1000),
TableName VARCHAR(1000))
INSERT #output
EXEC sp_msforeachdb 'use [?] SELECT ''?'',SCHEMA_NAME(schema_id), name
FROM sys.tables'
SELECT * FROM #output
仅作为提醒,sp_msforeachdb 存储过程是未记录的,您不应该将其用于生产代码。如果只是为了快速查找,那么可以使用它,但对于生产代码,请编写自己的版本。
另请参阅 Aaron Bertrand 的这些文章:
sp_msforeachdb
保持非常谨慎 - https://sqlblog.org/blogs/aaron_bertrand/archive/2010/12/29/a-more-reliable-and-more-flexible-sp-msforeachdb.aspx&http://www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-and-flexible-spmsforeachdb/。 - Aaron BertrandSELECT DB_NAME() db_name
, SCHEMA_NAME(schema_id) schema_name
, name table_name
FROM OtherDB.sys.tables --The OtherDB is to specify that I am running
--this for a different database than the one
--I'm logged in to for my current session.
OtherDB.sys.tables
,但DB_NAME()
总是返回我所在的当前数据库。是的,我可以在开头加上USE OtherDB
,但似乎应该有另一种方法。我查看了我能找到的每个sys视图,但从来没有找到任何将sys.databases
和sys.tables
链接起来的东西。
我最终发现的是SQL Server的INFORMATION_SCHEMA.TABLES
。这个视图将数据库名称作为第一列(称为TABLE_CATAOLG
)。
SELECT TABLE_CATALOG
, TABLE_SCHEMA
, TABLE_NAME
, TABLE_TYPE
FROM INFORMATION_SCHEMA.TABLES
SELECT a.TABLE_CATALOG
, a.TABLE_SCHEMA
, a.TABLE_NAME
, a.TABLE_TYPE
, b.TABLE_CATALOG
, b.TABLE_SCHEMA
, b.TABLE_NAME
, b.TABLE_TYPE
FROM OneDatabase.INFORMATION_SCHEMA.TABLES a
FULL OUTER JOIN TwoDatabase.INFORMATION_SCHEMA.TABLES b
ON a.TABLE_SCHEMA = b.TABLE_SCHEMA
AND a.TABLE_NAME = b.TABLE_NAME
SELECT DB_NAME(), SCHEMA_NAME(schema_id), name FROM sys.tables;
如果您的意图是从所有数据库中提取所有名称,我个人更喜欢像这样的动态SQL,而不是使用sp_msforeachdb
:
DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql += CHAR(13) + CHAR(10) + 'UNION ALL
SELECT ''' + name + ''', s.name, t.name
FROM ' + QUOTENAME(name) + '.sys.tables AS t
INNER JOIN ' + QUOTENAME(name) + '.sys.schemas AS s
ON t.schema_id = s.schema_id'
FROM sys.databases
WHERE database_id > 4;
SET @sql = STUFF(@sql, 1, 13, '');
PRINT @sql;
-- EXEC sp_executesql @sql;