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.  

Microsoft SQL ServerMicrosoft SQL Server 2008Microsoft SQL Server 2005

Avatar of undefined
Last Comment

8/22/2022 - Mon

try for example

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

Creating your own sp_MSforeach stored procedure
Scott Pletcher

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.
Scott Pletcher

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy