Link to home
Start Free TrialLog in
Avatar of ttist25
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.  

Thanks!
Avatar of EugeneZ
EugeneZ
Flag of United States of America image

try for example




USE <YOURDBName>
GO
exec sp_MSforeachtable 'SELECT  ''?'' Tablename, count(*) ''Rows'' FROM ?';


Creating your own sp_MSforeach stored procedure
http://www.sqlservercentral.com/articles/Administration/sp_msforeachworker/711/
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.
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
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
ttist25

ASKER

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.