时间戳

首页 > 解决方案 > 在所有数据库上查询 INFORMATION_SCHEMA.COLUMNS？

sql-server - 在所有数据库上查询 INFORMATION_SCHEMA.COLUMNS？

问题描述

有没有办法在所有数据库中查询 INFORMATION_SCHEMA.COLUMNS ？看来USE db必须指定。

我试过这个，但在使用 PowerShell 时它什么也不返回Invoke-Sqlcmd。

IF DB_ID('db') IS NOT NULL
BEGIN
    SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME LIKE 'BID_BY_CPT_SPECIAL'
    ORDER BY TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,ORDINAL_POSITION
    ;
END

此查询在 PowerShell 脚本中使用。

$q = @"
DROP TABLE IF EXISTS #Temp;
SELECT DatabaseName=cast('' as varchar(100))
     , TABLE_NAME
     , COLUMN_NAME
     , DATA_TYPE
     , ORDINAL_POSITION
 Into  #Temp
 FROM INFORMATION_SCHEMA.COLUMNS
 Where 1=0

Declare @SQL varchar(max) = '
USE ? ;

Insert Into #Temp
SELECT DatabaseName=''?''
     , TABLE_NAME
     , COLUMN_NAME
     , DATA_TYPE
     , ORDINAL_POSITION
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_NAME LIKE ''BID_BY_CPT_SPECIAL''
 ORDER BY TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,ORDINAL_POSITION
'

EXEC sp_MSforeachdb @SQL 

Select * From #Temp
"@

foreach ($db in $DatabaseInstances) {
    $r = Invoke-Sqlcmd -Query $q -ServerInstance $db

=== 更新

此查询正在使用Invoke-Sqlcmd.

$q = @"
DROP TABLE IF EXISTS #Temp;
SELECT DatabaseName=cast('' as varchar(100))
    ,TABLE_NAME
    ,COLUMN_NAME
    ,DATA_TYPE
    ,ORDINAL_POSITION
    ,CHARACTER_MAXIMUM_LENGTH
    ,NUMERIC_PRECISION
    ,NUMERIC_SCALE
    ,IS_NULLABLE
Into  #Temp
FROM INFORMATION_SCHEMA.COLUMNS
Where 1=0

Declare @SQL varchar(max) = '
USE ? ;

Insert Into #Temp
SELECT DatabaseName=''?''
    ,TABLE_NAME
    ,COLUMN_NAME
    ,DATA_TYPE
    ,ORDINAL_POSITION
    ,CHARACTER_MAXIMUM_LENGTH
    ,NUMERIC_PRECISION
    ,NUMERIC_SCALE
    ,IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_NAME LIKE ''$TableName''
 ORDER BY TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,ORDINAL_POSITION
'

EXEC sp_MSforeachdb @SQL 

Select * From #Temp
"@

标签： sql-serverpowershell

解决方案

看看sp_MSforeachdb

这会将数据收集到一个临时表中。就个人而言，我会把你WHERE带到决赛SELECT

例子

SELECT DatabaseName=cast('' as varchar(100))
     , TABLE_NAME
     , COLUMN_NAME
     , DATA_TYPE
     , ORDINAL_POSITION
 Into  #Temp
 FROM INFORMATION_SCHEMA.COLUMNS
 Where 1=0

Declare @SQL varchar(max) = '
USE ? ;

Insert Into #Temp
SELECT DatabaseName=''?''
     , TABLE_NAME
     , COLUMN_NAME
     , DATA_TYPE
     , ORDINAL_POSITION
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_NAME LIKE ''BID_BY_CPT_SPECIAL''
 ORDER BY TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,ORDINAL_POSITION
'

EXEC sp_MSforeachdb @SQL 

Select * From #Temp

推荐阅读