遍历数据库中的所有表和列，并为每个列返回不同的值

我试图将所有模式、表、列、不同列值和每个不同值的计数输出到一个新表。我从information.schema_columns中找到了前三列(模式，表，列)，但是最后两列不那么容易。

我尝试使用游标输出不同的列值和每个不同值的计数，但只能找到每列不同值的总数。

但是，我需要按每个值分组，并输出值本身和每个值的计数。

例如:

ValueCount

简体:

DECLARE @SQL NVARCHAR(max) = '';
SELECT @SQL = @SQL 
+ 'SELECT ''' + TABLE_SCHEMA + ''' AS SCHEMA_NAME,  '''
+ TABLE_NAME + ''' AS TABLE_NAME,  ''' 
+ COLUMN_NAME + ''' AS COL_NAME,  ''' 
+ DATA_TYPE + ''' AS DATA_TYPE, CAST([' 
+ COLUMN_NAME + '] AS NVARCHAR(4000)) AS [VALUE], COUNT(*) AS COUNT_VALUE FROM ['
+ TABLE_SCHEMA + '].[' + TABLE_NAME 
+ '] GROUP BY [' + COLUMN_NAME + '] UNION ALL '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE NOT IN ('text', 'ntext', 'image')
AND CHARACTER_MAXIMUM_LENGTH >= 0;
SET @SQL = LEFT(@SQL, LEN(@SQL) - 10);
EXEC (@SQL);

这段代码由RoughPlace做的正是我所寻找的!

create Table #Temp 
(tableName varchar(100),
columnName varchar(100),
value varchar(1000),
distinctItems int)
Declare @tabName as varchar(100)
Declare @colName as varchar(100)
Declare @tabid as int
Declare cursorTables Cursor
for 
select t.object_id , t.name , c.name from sys.tables t inner join sys.columns c on     t.object_id = c.object_id
open cursorTables 
Fetch Next from cursorTables into 
@tabid,@tabName,@colName

while @@Fetch_Status = 0 
Begin
declare @query as nVarchar(1000)
set @query  = 'Insert into #Temp SELECT    ''' + @tabName + '''  , '''+ @colName +''', '     + @colName + ',  COUNT([' + @colName +']) AS Expr1 FROM          [' + @tabName+ '] group by     [' + @colName + ']'
print @query
exec sp_executesql @query
Fetch Next from cursorTables into 
@tabid,@tabName,@colName

End 
Close cursorTables
Deallocate cursorTables
select * from #temp
drop table #temp

相关内容

最新更新

热门标签：