Mssql 查询所有表的列（在所有数据库中）

需要单独一个库的，在where中注明即可

​
DECLARE @SQL varchar(max)
SET @SQL=''
SELECT @SQL=@SQL+'UNION
select
a.TABLE_CATALOG, 
a.TABLE_NAME,
a.COLUMN_NAME,
    CASE WHEN a.CHARACTER_MAXIMUM_LENGTH IS Null 
    THEN a.DATA_TYPE 
  ELSE CASE WHEN a.CHARACTER_MAXIMUM_LENGTH=-1 
    THEN a.DATA_TYPE+''(max)''
  ELSE a.DATA_TYPE+''(''+cast(a.CHARACTER_MAXIMUM_LENGTH as varchar(50))+'')''
    END END AS TYPE, 
  CASE WHEN b.COLUMN_NAME IS NULL THEN 0 ELSE 1 END AS IS_PRIMARY
from '+d.name+'.INFORMATION_SCHEMA.COLUMNS a 
    LEFT JOIN '+d.name+'.INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE b 
    ON a.TABLE_CATALOG=b.TABLE_CATALOG 
    AND a.TABLE_SCHEMA=b.TABLE_SCHEMA 
    AND a.TABLE_NAME=b.TABLE_NAME 
    AND a.COLUMN_NAME=b.COLUMN_NAME
'
FROM sys.databases d  --where d.name!=''
SELECT @SQL=RIGHT(@SQL,LEN(@SQL)-5)+'order by 1,3'
--print @SQL
EXEC (@SQL)
​
​