如何列出从特定表中的列具有特定类型的所有数据库？

问题描述：

我想列出具有特定名称和所有数据库从一个特定的表，其列有具体类型，如日期如何列出从特定表中的列具有特定类型的所有数据库？

要获得具有特定名称的所有数据库：

SELECT [name] FROM [sysdatabases] WHERE [name] like 'myDBname_%'

为了得到一个特定的类型为所有列：

select TABLE_CATALOG,TABLE_NAME, COLUMN_NAME, DATA_TYPE from myDBname_something.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'A_SPECIFIC_TABLE_NAME'

如何链接这两个请求？换句话说，我如何得到第一个请求的每个结果的第二个请求的结果？

数据库没有列。一个数据库有表格，表格有列。你是否将数据库与表相混淆？ – JohnHC

你需要光标+动态sql（'sp_executesql'）来生成第二个查询 –

答

可以执行第二个查询作为系统进程内的参数，sp_MSforeachdb

EXECUTE master.sys.sp_MSforeachdb 'select TABLE_CATALOG,TABLE_NAME, COLUMN_NAME, DATA_TYPE from [?].INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = ''A_SPECIFIC_TABLE_NAME'' '

如果你要筛选只有某些数据库，你可以在TABLE_CATALOG列中添加额外的过滤器，第二个查询。 TABLE_CATALOG是数据库名称。

如果你想确保你不会从没有满足过滤任何列的数据库得到任何空的结果，您可以在查询前加上一个IF EXISTS（）：

EXECUTE master.sys.sp_MSforeachdb 'IF EXISTS(SELECT * from [?].INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = ''A_SPECIFIC_TABLE_NAME'') 
     select TABLE_CATALOG,TABLE_NAME, COLUMN_NAME, DATA_TYPE from [?].INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = ''A_SPECIFIC_TABLE_NAME'' '

非常好，现在我该如何删除空结果？ –

当我运行你的请求时，我得到了43个合格数据库中的222个行。我有一堆TABLE_CATALOG，TABLE_NAME，COLUMN_NAME，DATA_TYPE全部为空的行 –

如何从结果数据库中删除过滤器不返回任何内容？ –

答

使用动态SQL在一个循环中获取数据库的名称或使用动态SQL与游标

方法1：使用循环

Declare @ssql nvarchar(max),@DBName varchar(100),@rowcount int,@datatype varchar(10) 

Create table #temp(id int identity(1,1) ,DBName varchar(100)) 

set @rowcount=0 
set @datatype='int' 

Insert into #temp(DBName) 
SELECT [name] FROM sys.[sysdatabases] WHERE [name] like 'MyDBName%' 

while @rowcount<=1 
begin 

select @DBName=DBName from #temp where [email protected] 

set @ssql=N'select TABLE_CATALOG,TABLE_NAME, COLUMN_NAME, DATA_TYPE from '[email protected]+'.INFORMATION_SCHEMA.COLUMNS 
where TABLE_NAME in (select TABLE_NAME from '[email protected]+'.INFORMATION_SCHEMA.TABLES where TABLE_TYPE=''BASE TABLE'') 
and DATA_TYPE='''[email protected]+'''' 

--select @ssql 
exec sp_executesql @ssql 

set @[email protected]+1 
end

方法2：光标

Declare @ssql nvarchar(max),@DBName varchar(100),@rowcount int,@datatype varchar(10) 
set @rowcount=0 
set @datatype='int' 
DECLARE cursorDBName CURSOR -- Declare cursor 
FOR 

SELECT [name] FROM sys.[sysdatabases] WHERE [name] like 'MyDBName%' 
OPEN cursorDBName -- open the cursor 

FETCH NEXT FROM cursorDBName 

INTO @DBName 

WHILE @@FETCH_STATUS = 0 

BEGIN 

set @ssql=N'select TABLE_CATALOG,TABLE_NAME, COLUMN_NAME, DATA_TYPE from '[email protected]+'.INFORMATION_SCHEMA.COLUMNS 
where TABLE_NAME in (select TABLE_NAME from '[email protected]+'.INFORMATION_SCHEMA.TABLES where TABLE_TYPE=''BASE TABLE'') 
and DATA_TYPE='''[email protected]+'''' 

--select @ssql 
exec sp_executesql @ssql 

FETCH NEXT FROM cursorDBName 

    INTO @DBName 
END 

CLOSE cursorDBName -- close the cursor 

DEALLOCATE cursorDBName

答

这是另一个不使用任何循环或光标的选项。我正在使用sys.database来生成单个select语句。除非你拥有大量的数据库，否则这个性能不会太高，如果有的话，性能会超过基于循环的解决方案。但我真的不喜欢游标和循环，这是构建动态sql以避免使用循环的一种相当简单的方法。

DECLARE @SQL NVARCHAR(MAX) = '' --need to initialize to empty string 
    , @TableToFind sysname = 'YourTableName' 

SELECT @SQL = @SQL + 'select TABLE_CATALOG, TABLE_NAME, COLUMN_NAME, DATA_TYPE from [' + db.name + '].INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableToFind UNION ALL ' 
FROM sys.databases db 
WHERE [state] = 0 --only gets online multi user databases 
    AND name LIKE 'myDBname_%' 

IF LEN(@SQL) > 0 
BEGIN 
    SELECT @SQL = LEFT(@SQL, LEN(@SQL) - 10) 

    SELECT @SQL 
    --uncomment the line below when you are comfortable the dynamic sql generated will function. 
    --EXEC sp_executesql @SQL, N'@TableToFind sysname', @TableToFind = @TableToFind 
END

如何列出从特定表中的列具有特定类型的所有数据库？

相关推荐