Looping through column names with dynamic SQL

loop through column names in sql
sql loop through column values
sql server loop through table rows without cursor
sql server loop through columns of temp table
sql loop through select results
loop through user defined table type in sql server
sql looping through columns
for loop in sql

I just came up with an idea for a piece of code to show all the distinct values for each column, and count how many records for each. I want the code to loop through all columns.

Here's what I have so far... I'm new to SQL so bear with the noobness :)

Hard code:

  select [Sales Manager], count(*)
  from  [BT].[dbo].[test]
  group by [Sales Manager]
  order by 2 desc

Attempt at dynamic SQL:

Declare @sql varchar(max),
@column as varchar(255)

    set @column = '[Sales Manager]'
    set @sql = 'select ' + @column + ',count(*) from [BT].[dbo].[test] group by ' + @column + 'order by 2 desc'

    exec (@sql)

Both of these work fine. How can I make it loop through all columns? I don't mind if I have to hard code the column names and it works its way through subbing in each one for @column.

Does this make sense?

Thanks all!

You can use dynamic SQL and get all the column names for a table. Then build up the script:

Declare @sql varchar(max) = ''
declare @tablename as varchar(255) = 'test'

select @sql = @sql + 'select [' + c.name + '],count(*) as ''' + c.name +  ''' from [' + t.name + '] group by [' + c.name + '] order by 2 desc; ' 
from sys.columns c
inner join sys.tables t on c.object_id = t.object_id
where t.name = @tablename

EXEC (@sql)

Change @tablename to the name of your table (without the database or schema name).

Loop through column names in SQL Server, You don't need Dynamic SQL for this. You can just approach the problem from a different (and thankfully also set-based) approach. Just cross-join your query to� Usually cursors have less performance than an equivalent loop using a WHILE loop or CTE. Pros and Cons of Using a While Loop to Iterate Through Table Rows in SQL Server. There are also benefits to use a WHILE loop compared to a cursor. While loops are faster than cursors. While loops use less locks than cursors.

This is a bit of an XY answer, but if you don't mind hardcoding the column names, I suggest you do just that, and avoid dynamic SQL - and the loop - entirely. Dynamic SQL is generally considered the last resort, opens you up to security issues (SQL injection attacks) if not careful, and can often be slower if queries and execution plans cannot be cached.

If you have a ton of column names you can write a quick piece of code or mail merge in Word to do the substitution for you.


However, as far as how to get column names, assuming this is SQL Server, you can use the following query:

SELECT c.name
FROM sys.columns c
WHERE c.object_id = OBJECT_ID('dbo.test')

Therefore, you can build your dynamic SQL from this query:

SELECT 'select ' 
    + QUOTENAME(c.name) 
    + ',count(*) from [BT].[dbo].[test] group by ' 
    + QUOTENAME(c.name)  
    + 'order by 2 desc'
FROM sys.columns c
WHERE c.object_id = OBJECT_ID('dbo.test')

and loop using a cursor.

Or compile the whole thing together into one batch and execute. Here we use the FOR XML PATH('') trick:

DECLARE @sql VARCHAR(MAX) = (
    SELECT ' select ' --note the extra space at the beginning
        + QUOTENAME(c.name) 
        + ',count(*) from [BT].[dbo].[test] group by ' 
        + QUOTENAME(c.name)  
        + 'order by 2 desc'
    FROM sys.columns c
    WHERE c.object_id = OBJECT_ID('dbo.test')
    FOR XML PATH('')
)

EXEC(@sql)

Note I am using the built-in QUOTENAME function to escape column names that need escaping.

Looping through column names and make alias names using , Looping through column names and make alias names using CURSOR RRS feed � SQL 2011 7:54 PM TSQL question (From:SQL Server Database Engine) But these tables are dynamically generated from systables. DVLP SQL> DVLP SQL>declare 2 tab_name constant varchar2(32) := 'foo'; 3 not_null number; 4 begin 5 for x in (select column_name from all_tab_columns where table_name = upper(tab_name)) loop 6 dbms_output.put('Checking '||tab_name||'.'||x.column_name); 7 begin 8 execute immediate 'select 1 from (select 1 from '||tab_name|| 9 ' where '||x.column_name||' is not null) where rownum = 1' into not_null; 10 dbms_output.put_line('.'); 11 exception when NO_DATA_FOUND then 12 dbms_output.put_line

You want to know the distinct coulmn values in all the columns of the table ? Just replace the table name Employee with your table name in the following code:

declare @SQL nvarchar(max)
set @SQL = ''
;with cols as (
select Table_Schema, Table_Name, Column_Name, Row_Number() over(partition by Table_Schema, Table_Name
order by ORDINAL_POSITION) as RowNum
from INFORMATION_SCHEMA.COLUMNS
)

select @SQL = @SQL + case when RowNum = 1 then '' else ' union all ' end
+ ' select ''' + Column_Name + ''' as Column_Name, count(distinct ' + quotename (Column_Name) + ' ) As DistinctCountValue, 
count( '+ quotename (Column_Name) + ') as CountValue FROM ' + quotename (Table_Schema) + '.' + quotename (Table_Name)
from cols
where Table_Name = 'Employee' --print @SQL

execute (@SQL)

Update Table, Update Table - By Looping Through Field Name – Learn more on the You will have to either code a big long list or use dynamic sql for this. Therefore, you can build your dynamic SQL from this query: SELECT 'select ' + QUOTENAME(c.name) + ',count(*) from [BT].[dbo].[test] group by ' + QUOTENAME(c.name) + 'order by 2 desc' FROM sys.columns c WHERE c.object_id = OBJECT_ID('dbo.test') and loop using a cursor.

SQL Server Loop through Table Rows without Cursor, Pros and Cons of Using Cursors to Iterate Through Table Rows in SQL Server cursor creation statement, you can update the columns within the cursor. with the name of the databases residing on a SQL Server instance without you need to dynamically change the iteration condition inside the loop. SQL WHILE loop provides us with the advantage to execute the SQL statement(s) repeatedly until the specified condition result turn out to be false. In the following sections of this article, we will use more flowcharts in order to explain the notions and examples.

how to loop through all the columns in a table Solutions, I want to loop through all the columns and extract all the records where 10 of the columns are NOT empty/null here is how i made it using dynamic SQL: [ name] + ' is null then 1 else 0 end + ' + char(10) + char(13) from sysobjects inner join� The code loops through the array of new_cols, and for each value, it uses DBMS_TF.column_metadata_t to define a new column of type NUMBER. Finally, lines 26–28 return the new column array to the client. This adds the new columns to the query’s output. Now that I have defined the new columns, it’s time to set their values.

Loop through list of fields, check against lookup table, Native dynamic SQL (in a PL/SQL anonymous block): Loop through the list of fields in FIELD_DOMAIN_ENG_VW . For each field name in a list of fields, get the unique values (union) the table to generate a query for each column in the new table and outputs them as part of a new package definition. SQL stands for Structured Query Language. It is specifically designed to retrieve and manipulate data from relational databases. PL/SQL is a procedural extension of SQL and is well integrated with the latter. This language contains important programming features which are combined with SQL to create useful data driven applications. PL/SQL code can be called from […]

Comments
  • I tried this code... It seemed to run but am I supposed to add something? I don't see a loop in there? Cheers,
  • You don't need a loop, all the queries for all columns are in @sql variable.
  • Hey Szymon. Okay I think I get it... haha. I get 'command completed succesfully' but no result is shown... What part of your code do I have to change to my own values?
  • Change @tablename to the name of your table (without the database or schema name).
  • Ah right. So copy your code exactly... Nothing happens unfortunately. :( The results should show up in the results window right?