问题描述:

I NEVER do complicated stuff in SQL - until now...

I have a database with over 2000 tables, each table has about 200 columns.

I need to get a list of all the columns in one of those tables that are populated at least 1 time.

I can get a list of all the columns like this:

SELECT [name] AS [Column name]

FROM syscolumns with (nolock)

WHERE id = (SELECT id FROM sysobjects where name like 'DOCSDB_TDCCINS')

But I need only the columns that are populated 1 or more times.

Any help would be appreciated.

网友答案:

Here is how I would do it, first run this:

SELECT 'SELECT '''+syscolumns.name+''' FROM '+sysobjects.name+' HAVING COUNT('+syscolumns.name+') > 0'
FROM syscolumns with (nolock) 
JOIN sysobjects with (nolock) ON syscolumns.id = sysobjects.id
WHERE syscolumns.id = (SELECT id FROM sysobjects where name like 'Email')

Copy all the select statements and run them.

This will give you a list of the column names without nulls.

(nb I did not test because I don't have an SQL server available right now, so I could have a typo)

网友答案:

It may be also be useful to count the non-null instances, obviously 0 or not 0 was your initial question, and counting the instances versus exists not/exists will be slower.

select 'union select ''' + Column_Name + ''',count(*)'
  + ' from ' + table_name
  + ' where ' + column_name + ' is not null'
from 
(
select * from information_schema.columns with (nolock)
    where Is_Nullable = 'YES'
AND Table_Name like 'DOCSDB_TDCCINS'
) DD

Then remove the superfluous leading 'union' and run the query

网友答案:

A different idea is to create a dynamic unpivot for every table.

Declare @q NVarchar(MAX) = NULL

;With D AS (
    SELECT TABLE_SCHEMA
         , TABLE_NAME
         , STUFF((SELECT ', ' + QUOTENAME(ci.COLUMN_NAME) 
                  FROM   INFORMATION_SCHEMA.COLUMNS ci
                  WHERE  (ci.TABLE_NAME = c.TABLE_NAME) 
                    AND  (ci.TABLE_SCHEMA = c.TABLE_SCHEMA)
                  FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)')
               ,1,2,'') AS _Cols
         , STUFF((SELECT ', Count(' + QUOTENAME(ci.COLUMN_NAME) + ') ' 
                              + QUOTENAME(ci.COLUMN_NAME) 
                  FROM   INFORMATION_SCHEMA.COLUMNS ci
                  WHERE  (ci.TABLE_NAME = c.TABLE_NAME) 
                    AND  (ci.TABLE_SCHEMA = c.TABLE_SCHEMA)
                  FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)')
               ,1,2,'') AS _ColsCount
    FROM INFORMATION_SCHEMA.COLUMNS c
    GROUP BY TABLE_SCHEMA, TABLE_NAME
)
SELECT @q = COALESCE(@q + ' UNION ALL ', '') + '
SELECT ''' + TABLE_SCHEMA + ''' _Schema, ''' + TABLE_NAME + ''' _Table, _Column 
FROM   (SELECT ' + _ColsCount + ' from ' + TABLE_SCHEMA + '.' + TABLE_NAME + ') x
       UNPIVOT 
       (_Count FOR _Column IN (' + _Cols + ')) u
WHERE  _Count > 0'
FROM D

exec sp_executesql @q

In the CTE _Cols returns the comma separated quoted name of the columns of the table, while _ColsCount returns the same list with the COUNT function, for example for a table of mine a row of D is

TABLE_SCHEMA | TABLE_NAME      | _Cols                        | _ColsCount
------------- ----------------- ------------------------------ -----------------------------------------------------------------------------
dbo          | AnnualInterests | [Product_ID], [Rate], [Term] | Count([Product_ID]) [Product_ID], Count([Rate]) [Rate], Count([Term]) [Term]

while the main query trasform this line in the UNPIVOT to return the columns in rows

SELECT 'dbo' _Schema, 'AnnualInterests' _Table, _Column 
FROM   (SELECT Count([Product_ID]) [Product_ID], Count([Term]) [Term]
             , Count([Rate]) [Rate] from dbo.AnnualInterests) x
       UNPIVOT 
       (_Count FOR _Column IN ([Product_ID], [Term], [Rate])
WHERE  _Count > 0

using the string variable concatenation and sp_executesql to run the string complete the script.

网友答案:

Hope you can achieve this by a simple alteration on your code like

SELECT [name] AS [Column name] 
FROM syscolumns with (nolock) 
WHERE id = (SELECT id FROM sysobjects where name like 'DOCSDB_TDCCINS')
and (select count(*) from DOCSDB_TDCCINS)>0
相关阅读:
Top