我有一个包含 1500 个左右表的 SQL Server 数据库.在一个查询中，是否有一种快速方法来确定每个表中每一列的

Question

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM   INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA = 'dbo'
order by table_name 

因此，我想要一个额外的列，显示每个表中每个字段的最大值。我只对日期真正感兴趣(因为我想找出哪些表已经过时(，但我可以稍后过滤掉它们。

Answer 1

这将生成您需要的查询：

SELECT concat('select ''', [TABLE_NAME], ''' as [table], ''', [COLUMN_NAME], ''' as [column], max([',[COLUMN_NAME],']) as [maxdate] from [', [TABLE_NAME], ']')
FROM [INFORMATION_SCHEMA].[COLUMNS]
WHERE [TABLE_SCHEMA] = 'dbo' 
AND [DATA_TYPE] = 'datetime'
ORDER BY [TABLE_NAME]

动态执行它们(在它们之间粘贴一些union all(，你就在那里。

Answer 2

下面的查询会给你确切的答案

SELECT 'SELECT ''' +B.TABLE_NAME+''' AS TableName,'''+COLUMN_NAME+''' AS ColumnName,MAX('+QUOTENAME(COLUMN_NAME)+') AS MaxDate FROM '+ QUOTENAME(B.TABLE_NAME)+' UNION ALL '
FROM [INFORMATION_SCHEMA].[TABLES] A
INNER JOIN [INFORMATION_SCHEMA].[COLUMNS] B ON A.TABLE_NAME=B.TABLE_NAME
WHERE B.[TABLE_SCHEMA] = 'dbo' 
AND [DATA_TYPE] = 'datetime' AND A.TABLE_TYPE='BASE TABLE'
ORDER BY B.[TABLE_NAME]

从生成的脚本中删除最后一个 UNION ALL。

Answer 3

这些是另一种解决方案：

使用动态 SQL：

DECLARE @stm nvarchar(max)
SELECT @stm = (
SELECT 
'SELECT ''' + [TABLE_NAME] +''' AS TableName, ''' + [COLUMN_NAME] +''' AS ColumnName, MAX([' + [COLUMN_NAME] + ']) AS DataValue '+
'FROM [' + [TABLE_NAME] + '] UNION ALL '
FROM [INFORMATION_SCHEMA].[COLUMNS]
WHERE ([TABLE_SCHEMA] = 'dbo') AND ([DATA_TYPE] = 'datetime')
ORDER BY TABLE_NAME, COLUMN_NAME
FOR XML PATH('')
)
SET @stm = SUBSTRING(@stm, 1, LEN(@stm)-10)
EXEC (@stm)

使用光标：

-- Variable declaration
DECLARE @tablename nvarchar(128)
DECLARE @columnname nvarchar(128)
DECLARE @stm nvarchar(max)
-- Cursor declaration
DECLARE DATEVALUES CURSOR GLOBAL FORWARD_ONLY READ_ONLY FOR
SELECT TABLE_NAME, COLUMN_NAME
FROM   INFORMATION_SCHEMA.COLUMNS
WHERE (TABLE_SCHEMA = 'dbo') AND (DATA_TYPE = 'datetime')
ORDER BY TABLE_NAME, COLUMN_NAME
-- Tables and columns
SET @stm = N''
OPEN DATEVALUES
FETCH NEXT FROM DATEVALUES INTO @tablename, @columnname
WHILE (@@FETCH_STATUS <> -1) BEGIN
IF @stm <> N'' SET @stm = @stm + N' UNION ALL '
SET @stm = @stm + N'SELECT '''  + @tablename + ''' AS TableName, ''' + @columnname +''' AS ColumnName, MAX([' + @columnname + ']) AS MaxDate FROM ['  + @tablename + ']'
FETCH NEXT FROM DATEVALUES INTO @tablename, @columnname
END
CLOSE DATEVALUES
DEALLOCATE DATEVALUES
-- Execution
EXEC(@stm)

笔记： 在 SQL Server 2005 和 2012 Express Edition 上测试。在 SQL Server 2005 中，允许的最大表名数为 256。