526互联

sqlserver查询库中所有表的字段并进行拼接

发布时间 2023-11-05 15:22:14作者: panbin_2006
-- 查询库中所有表的字段信息
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo'
ORDER BY TABLE_NAME, ORDINAL_POSITION;

-- 查询库中所有表的字段并进行拼接
SELECT TABLE_NAME, 
       STUFF((SELECT ', ' + COLUMN_NAME 
              FROM INFORMATION_SCHEMA.COLUMNS 
              WHERE TABLE_SCHEMA = 'dbo' 
                AND TABLE_NAME = t.TABLE_NAME 
              FOR XML PATH('')), 1, 2, '') AS COLUMNS
FROM INFORMATION_SCHEMA.COLUMNS AS t
WHERE TABLE_SCHEMA = 'dbo'
GROUP BY TABLE_NAME;