如何加入sys.databases、sys.tables和sys.columns

Question

我必须检查sql server实例的数据库子集中的表子集中是否存在值。请注意，我需要这样做，因为我有30个数据库具有相同的模式名称和类似的结构。单独查询所有数据库是浪费时间。

查询为现有表生成了正确的代码，但对表中是否存在列的额外检查失败。某些表中的列不存在，因此生成的代码不得包含对没有该列的表的查询。

为了解决这个问题，我需要真正找到一种方法，将sys.databases与sys.tables连接起来，然后再连接sys.columns。或者找到一种替代方法，以节省时间的方式查询所有需要的数据库。

SET NOCOUNT ON;
IF OBJECT_ID (N'tempdb.dbo.#temp') IS NOT NULL
DROP TABLE #temp
CREATE TABLE #temp
(
exist INT
, DB VARCHAR(50)
, tbname VARCHAR(500)
)
/*tables common root, 
all tables i need to query start with this prefix and a number between 1 and 50
and some resulting tables do not exist
ex: dbo.Z_WBL_ASCHEDA23 exist in wbcto, while dbo.Z_WBL_ASCHEDA23 does not exist in db wbgtg    
*/
DECLARE @TableName NVARCHAR(200) 
SELECT @TableName = 'dbo.Z_WBL_ASCHEDA'
DECLARE @SQL NVARCHAR(MAX)
;WITH n(n) AS
(
SELECT 1
UNION ALL
SELECT n+1 FROM n WHERE n < 50
)
SELECT @SQL = STUFF((
SELECT CHAR(13)+'SELECT COUNT(1), ''' + db.name + ''', '''+
@TableName+CONVERT(VARCHAR, n.n)+'''  FROM ' +@TableName+CONVERT(VARCHAR, n.n) 
+ ' WHERE COALESCE(s_dettagli,'''') = ''CONTROLLATO'' '
+CHAR(13)
FROM sys.databases db
INNER JOIN n ON 1=1
INNER JOIN sys.tables t ON OBJECT_ID(db.name + '.' + @TableName+CONVERT(VARCHAR, n.n)) IS NOT NULL
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID and c.name = 's_dettagli' 
/*join on columns not working, generates sql for tables without 's_dettagli' column and query fails*/
WHERE db.name like 'wb%' --check only databases starting with 'wb'
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
select @SQL
INSERT INTO #temp (exist, DB, tbname)              
EXEC sys.sp_executesql @SQL
SELECT * 
FROM #temp t
where exist <> 0

EDIT：添加一些从查询生成的sql

SELECT COUNT(1), 'wb360', 'dbo.Z_WBL_ASCHEDA23'  FROM wb360.dbo.Z_WBL_ASCHEDA23 WHERE COALESCE(s_dettagli,'') = 'CONTROLLATO'  
SELECT COUNT(1), 'Wbbim', 'dbo.Z_WBL_ASCHEDA32'  FROM Wbbim.dbo.Z_WBL_ASCHEDA32 WHERE COALESCE(s_dettagli,'') = 'CONTROLLATO'

第一个查询的表不包含"s_dettagli"列

第2版：解决方案

EXEC sp_MSforeachdb '
IF ''?'' not like ''wb%'' 
RETURN
USE [?]
EXEC sp_MSforeachtable 
@replacechar = ''!'', 
@command1 = ''SELECT ''''?'''' AS db_name, ''''!'''' AS table_name, COUNT(*) FROM !  '',
@whereand = '' And Object_id In (
Select t.Object_id 
From sys.objects t
INNER JOIN sys.columns c on c.Object_id = t.Object_id
Where t.name like ''''Z_WBL_ASCHEDA%''''
AND c.name = ''''s_dettagli'''' )'' '

Answer 1

Sys.columns可以使用object_id字段连接到Sys.tables(object_id是表本身的表示(。

sys.tables是在您正在查询的数据库的上下文中运行的，因此您无法看到另一个数据库中包含的表。sys.databases可以在实例上的任何数据库上运行，并允许您查看同一实例上的其他数据库。因此，您不需要将表连接到数据库(这也是sys.tables中没有database_id字段的原因(

我希望这能有所帮助。如有任何澄清，请告诉我。

Answer 2

我建议其他方法：

1. 在SSMS中使用已注册的服务器，并在此处的每个数据库上运行脚本
2. 在此处使用exec sys.sp_MSforeachdb
3. 使用sqlcmd和powershell切换数据库

Answer 3

我相信这个脚本可以帮助您：

SET NOCOUNT ON;
IF OBJECT_ID (N'tempdb.dbo.#Temp') IS NOT NULL
DROP TABLE #Temp
CREATE TABLE #Temp
(
exist INT
, DB VARCHAR(50)
, tbname VARCHAR(500)
)
DECLARE @SchemaName      NVARCHAR(200) 
DECLARE @TableName       NVARCHAR(200) 
DECLARE @ColumnName      NVARCHAR(200) 
DECLARE @SearchText      NVARCHAR(200) 
DECLARE @DBNameStartWith NVARCHAR(200) 
DECLARE @SQL             NVARCHAR(MAX)

SET @DBNameStartWith = 'wb'
SET @SchemaName      = 'dbo'
SET @TableName       = 'Z_WBL_ASCHEDA'
SET @ColumnName      = 's_dettagli'
SET @SearchText      = 'CONTROLLATO'

DECLARE @DatabaseName varchar(100) 
DECLARE Crsr CURSOR FOR 
SELECT name 
FROM MASTER.sys.sysdatabases 
WHERE name LIKE ''+@DBNameStartWith+'%'
OPEN Crsr 
FETCH NEXT FROM Crsr INTO @DatabaseName 
WHILE @@FETCH_STATUS = 0 
BEGIN 
IF ISNULL((SELECT COUNT(1) FROM SYS.TABLES T,SYS.COLUMNS C WHERE T.object_id=C.object_id AND T.name=@TableName AND C.name=@ColumnName),0)>0
BEGIN
SET  @SQL = '
IF EXISTS (SELECT 1 FROM '+@DatabaseName+'.SYS.TABLES T,'+@DatabaseName+'.SYS.COLUMNS C WHERE T.object_id=C.object_id AND T.name='''+@TableName+''' AND C.name='''+@ColumnName+''')
BEGIN          
SELECT COUNT(1),'''+@DatabaseName+''','''+@TableName+''' 
FROM '+@DatabaseName+'.'+@SchemaName+'.'+@TableName+'
WHERE '+@ColumnName+'=''' +@SearchText+'''
END'
PRINT(@SQL)
INSERT INTO #Temp
EXEC sp_executesql @SQL 
END
FETCH NEXT FROM Crsr INTO @DatabaseName 
END 
CLOSE Crsr 
DEALLOCATE Crsr 
SELECT * FROM #Temp