如何从不同的数据库访问表的行

Question

我正在创建一个以@dbname， @schemaname和@tablename为参数的存储过程，目的是计算所选表的每列中的空值，但是我不能访问来自不同数据库的数据。

我正在尝试这样做

create or alter proc testnulls(
@dbname     sysname = N'master', 
@schemaname sysname = N'dbo', 
@tablename  sysname = N'spt_values'
)
as
DECLARE @sql nvarchar(max) 
select @sql = @sql + '
sum(case when [' + c.name + '] is null then 1 else 0 end) as [' + c.name + '_NULLS]'
fROM sys.columns AS c 
INNER JOIN sys.all_objects AS t
ON c.[object_id] = t.[object_id]
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
where t.name = @tablename
and s.name=@schemaname
set @sql = @sql + ' from '+ QUOTENAME(@dbname)+  N'.'+ QUOTENAME(@schemaname)+ N'.' + QUOTENAME(@tablename)
select @sql

Answer 1

只是说，它不漂亮;动态SQL永远不会。我不想说谎，我真的不想解释这是在做什么，真是一团糟。如果需要的话，我建议使用我放入其中的PRINT语句来调试它。

CREATE OR ALTER PROC dbo.testnulls @dbname     sysname = N'master', 
@schemaname sysname = N'dbo', 
@tablename  sysname = N'spt_values' AS
BEGIN 
--DECLARE @dbname     sysname = N'master', 
--        @schemaname sysname = N'dbo', 
--        @tablename  sysname = N'spt_values';
DECLARE @SQL nvarchar(MAX), @DSQL nvarchar(MAX);
SET @SQL = N'SET @DSQL = N''SELECT '' + STUFF((SELECT N'', COUNT('' + QUOTENAME(c.[name]) + N'') AS '' + QUOTENAME(CONCAT(c.[name],N''_NULL''))
FROM ' + QUOTENAME(@dbname) + N'.sys.schemas s
JOIN ' + QUOTENAME(@dbname) + N'.sys.all_objects o ON s.schema_id = o.schema_id
JOIN ' + QUOTENAME(@dbname) + N'.sys.columns c ON o.object_id = c.object_id
WHERE s.[name] = @schemaname
AND o.[name] = @tablename
FOR XML PATH(N''''),TYPE).value(''(./text())[1]'',''nvarchar(MAX)''),1,2,N'''') + 
N''FROM ' + QUOTENAME(@dbname) + N'.' + QUOTENAME(@schemaname) + N'.' + QUOTENAME(@tablename) + N';''
EXEC sys.sp_executesql @DSQL, N''@schemaname sysname, @tablename sysname'', @schemaname, @tablename;';
EXEC sys.sp_executesql @SQL, N'@schemaname sysname, @tablename sysname, @DSQL nvarchar(MAX) OUTPUT', @schemaname, @tablename, @DSQL OUT;
PRINT @DSQL;
PRINT @SQL;
END;