Back to the story:\u00a0 What values do we typically use for flag columns in the data warehouse? 0 or 1? Y or N? Yes or No? T or F? True or False?\u00a0 What have we done in the past?\u00a0 (Another note: It's the data warehouse.\u00a0 The goal is to make it easy for the users.\u00a0 This eliminates 0\/1. I'd also eliminate Y\/N. Personal preference: True\/False<\/em>).<\/p>\n With a little information_schema hackery we can generate a nice little SQL script to get the answer for us.\u00a0 The basis is something like this:<\/p>\n This script will run fairly quickly.\u00a0 A few seconds or so.\u00a0 The last column of the results will contain the SQL script you need to run to get the actual values.<\/p>\n WARNING! Now here's where you need to be cautious!\u00a0 Review the results of the query above.\u00a0 Take a look at the row counts and consider whether doing an aggregation against some of your biggest tables is really necessary (or resource efficient).\u00a0 Customize the script as your needs dictate. Okay, cut and paste the select statement from the original results into a new window.\u00a0 It will looks like this (sorry you'll have to format your results yourself):<\/p>\n If you run this as is you'll get a syntax error.\u00a0 Scroll to the bottom of the query and remove the final \"UNION ALL\" (as well as any tables\/columns you don't want to include in the results).<\/p>\n In my case I also added a temp table so I could do faster analysis of the results.\u00a0 My final code looked like this:<\/p>\nSELECT c.TABLE_SCHEMA\r\n , c.TABLE_NAME\r\n , c.COLUMN_NAME\r\n , p.rows\r\n , 'select ''' + c.TABLE_SCHEMA + ''' as SchemaName, ''' + c.TABLE_NAME\r\n + ''' as TableName, ''' + c.COLUMN_NAME\r\n + ''' as ColumnName, count(1) as RowCnt, convert(varchar(200), '\r\n + c.COLUMN_NAME + ') as ExistingValues from ' + c.TABLE_SCHEMA + '.'\r\n + c.TABLE_NAME + ' group by ' + c.COLUMN_NAME + ' UNION ALL'\r\nFROM INFORMATION_SCHEMA.COLUMNS AS c\r\n JOIN INFORMATION_SCHEMA.tables AS t\r\n ON c.TABLE_CATALOG = t.TABLE_CATALOG\r\n AND c.TABLE_SCHEMA = t.TABLE_SCHEMA\r\n AND c.TABLE_NAME = t.TABLE_NAME\r\n JOIN sys.partitions AS p\r\n ON c.TABLE_SCHEMA = OBJECT_SCHEMA_NAME(p.object_id)\r\n AND c.TABLE_NAME = OBJECT_NAME(p.object_id)\r\n AND p.index_id < 2\r\nWHERE t.TABLE_TYPE = 'base table' \r\n AND COLUMN_NAME LIKE '%flag%'\r\nORDER BY p.rows DESC<\/pre>\n
\n<\/em><\/p>\nSELECT 'dbo' AS SchemaName\r\n , 'TableA' AS TableName\r\n , 'LikesHeathersFlag' AS ColumnName\r\n , COUNT(1) AS RowCnt\r\n , CONVERT(VARCHAR(200), LikesHeathersFlag) AS ExistingValues\r\nFROM dbo.TableA\r\nGROUP BY LikesHeathersFlag\r\nUNION ALL\r\nSELECT 'dbo' AS SchemaName\r\n , 'TableB' AS TableName\r\n , 'CancelFlag' AS ColumnName\r\n , COUNT(1) AS RowCnt\r\n , CONVERT(VARCHAR(200), CancelFlag) AS ExistingValues\r\nFROM dbo.TableB\r\nGROUP BY CancelFlag\r\nUNION ALL\r\nSELECT 'dbo' AS SchemaName\r\n , 'TableC' AS TableName\r\n , 'AnotherFlag' AS ColumnName\r\n , COUNT(1) AS RowCnt\r\n , CONVERT(VARCHAR(200), AnotherFlag) AS ExistingValues\r\nFROM dbo.TableC\r\nGROUP BY AnotherFlag\r\nUNION ALL<\/span><\/pre>\nSELECT 'dbo' AS SchemaName\r\n , 'TableA' AS TableName\r\n , 'LikesHeathersFlag' AS ColumnName\r\n , COUNT(1) AS RowCnt\r\n , CONVERT(VARCHAR(200), LikesHeathersFlag) AS ExistingValues\r\nINTO #FlagValues<\/span>\r\nFROM dbo.TableA\r\nGROUP BY LikesHeathersFlag\r\nUNION ALL\r\nSELECT 'dbo' AS SchemaName\r\n , 'TableB' AS TableName\r\n , 'CancelFlag' AS ColumnName\r\n , COUNT(1) AS RowCnt\r\n , CONVERT(VARCHAR(200), CancelFlag) AS ExistingValues\r\nFROM dbo.TableB\r\nGROUP BY CancelFlag\r\nUNION ALL\r\nSELECT 'dbo' AS SchemaName\r\n , 'TableC' AS TableName\r\n , 'AnotherFlag' AS ColumnName\r\n , COUNT(1) AS RowCnt\r\n , CONVERT(VARCHAR(200), AnotherFlag) AS ExistingValues\r\nFROM dbo.TableC\r\nGROUP BY AnotherFlag<\/pre>\n
![\"\"](\"http:\/\/www.sqldbpros.com\/wordpress\/wp-content\/uploads\/2012\/10\/allvaluesforacolumnacrossdatabase.jpg\" "\\"allvaluesforacolumnacrossdatabase\\"")
<\/a>