Link to home
Start Free TrialLog in
Avatar of ttist25

asked on

Declare SQL Variable for Column

Hey there,

I'm doing some initial review of data in SQL.  Things like:

SELECT COUNT(DISTINCT mycolmnname) FROM mytable
SELECT COUNT (mycolumnname) FROM mytable
SELECT TOP 10 (mycolumnname) FROM mytable

I've got a ton of tables and columns to do this with so I thought I could use a variable in my query rather than replacing the column name every time.  

Any help will be greatly appreciated.  

Avatar of EugeneZ
Flag of United States of America image

try for example

exec sp_MSforeachtable 'SELECT  ''?'' Tablename, count(*) ''Rows'' FROM ?';

Creating your own sp_MSforeach stored procedure
That's not a good approach, unless the tables are tiny.

You should count all columns within the same table in a single SELECT statement for all columns, not a separate SELECT for every column: that way, SQL only has to scan the table once to get all the totals.

I'll provide a script for that as soon as I can.
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ttist25


Hey guys - thanks for the EXCELLENT responses and sorry for the delay.  I've actually completed this task by hand (replacing mycolumnname each time).  

So, is there nothing simple I can put at the top of the query editor window that can allow me to do this?  

Excuse my faux syntax but something like:
USE mydb

SET @tablename = 'persons'
SET @columnname = 'lastname'

SELECT COUNT(DISTINCT @columnname) FROM @tablename
SELECT COUNT (@columnname) FROM @tablename
SELECT TOP 10 (@columnname) FROM @tablename


You know - just something simple.  

Thanks again for the responses.