Looping through column names with dynamic SQL

by

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).

More Related Contents:

  1. T-SQL dynamic pivot
  2. Dynamic SQL – EXEC(@SQL) versus EXEC SP_EXECUTESQL(@SQL)
  3. Call dynamic SQL from function
  4. unpivot with dynamic columns plus column names
  5. Syntax of for-loop in SQL Server
  6. How to delete the records by using SQL [closed]
  7. How do I obtain a Query Execution Plan in SQL Server?
  8. MySQL: Alternatives to ORDER BY RAND()
  9. IN vs ANY operator in PostgreSQL
  10. sql query to return differences between two tables
  11. SQL Server 2005 How Create a Unique Constraint?
  12. Is there any boolean type in Oracle databases?
  13. Physical vs. logical (hard vs. soft) delete of database record? [closed]
  14. sql server select first row from a group
  15. How to make SQL many-to-many same-type relationship table
  16. Convert Unixtime to Datetime SQL (Oracle)
  17. CASE WHEN statement for ORDER BY clause
  18. sqlite alter table add MULTIPLE columns in a single statement
  19. CTE error: “Types don’t match between the anchor and the recursive part”
  20. Automate Version number Retrieval from .Dtsx files
  21. Adding a new SQL column with a default value
  22. The text, ntext, and image data > types cannot be compared or sorted, except when using IS NULL or LIKE > operator
  23. Calculate working hours between 2 dates in PostgreSQL
  24. Why do we need GROUP BY with AGGREGATE FUNCTIONS?
  25. Comma Separated values with SQL Query
  26. Check whether string is a date Postgresql
  27. Using IF ELSE statement based on Count to execute different Insert statements
  28. Convert bigint to datetime
  29. Replacing NULL and empty string within Select statement
  30. Why isn’t row level security enabled for Postgres views?

Leave a Comment