Find Unused Columns in SQL

There is a database I do not have access to create stored procedures or modify any part of. It has tables with MANY columns, and many of those columns appear not to be used.

I would like to see which columns are used and which are not (every row has NULL value in that column).

I know I could manually do:

SELECT COUNT([columnX])
FROM [table]

for every single column, in every single table I'd like to know about, but there are hundreds of tables that may have hundreds of columns each. (One table has 786 columns.)

Is there a better way to find what I need with the permissions I have, without creating procedures, installing any tools, etc.

(The database is on SQL Server 2012).

Solution

This is the query I came up with. It gives not only the number of rows which have a value, but also the percent of all rows which have a value in each column.

It is specific to a single table (here called specifictable) because I was only interested in the columns of certain tables at the time, and it was easier to change the table name in two places than to make the query do it all itself.

First we declare some variables, get the count of all rows from specifictable:

DECLARE @sql NVARCHAR(MAX) = N'';
DECLARE @tot INT;
SELECT @tot = COUNT(*) FROM specifictable;

Then we concatenate together a big UNION SQL statement, separated by newlines (CHAR(13) + CHAR(10)). It selects the column name, its order in the table, the count of rows with a value in that column, and the percentage of all rows that is.

SELECT @sql += ' UNION ' + CHAR(13) + CHAR(10) 
    + 'SELECT '
        + '''' + COLUMN_NAME + ''' as Col, ' + cast(ORDINAL_POSITION as varchar) + ' as OrdinalPosition, '
        + ' count(' + COLUMN_NAME + ') as NumWithVal, '
        + '(count(' + COLUMN_NAME + ') / CAST(' + CAST(@tot as varchar(20)) + ' as decimal) * 100) as Percnt '
    + 'FROM ' + QUOTENAME(TABLE_NAME)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'specifictable';

Because it prepended each individual SELECT with a UNION, we trim off the entire first line, which only contains "UNION", and then add an ORDER BY.

SELECT @sql = SUBSTRING(@sql, CHARINDEX(char(10), @sql), LEN(@sql))     -- remove first line (extra union)
SELECT @sql += CHAR(13) + CHAR(10) + 'ORDER BY 3 DESC, OrdinalPosition'

Finally, it's executed (which requires the EXEC sp_executesql permission, which I thankfully have on this database).

EXEC sp_executesql @sql;