How to Generate Select all Columns with or Without Top X Rows From All Tables Query Dynamically in SQL Server - SQL Server / TSQL Tutorial

Scenario: Download Script

You are working as SQL Server Developer / TSQL developer. The project you are working is heavily concentrated on analysis and testing. You want to generate Select queries for all the tables in Database. One purpose of this query is comparing the columns if they are present in different environments. You don't want to generate select queries with "*". You want to create with Column Names. So you can run in QA,UAT or Production and verify all columns for all the tables does match in each environment for a database.

You should be able to handle the Top 10 part while generating the Select queries. You might want to generate all select queries with Top 100,Top 1000 or event you don't want to have top X at all.


Solution:

This query can be really helpful for meta data validation. Checking if all columns for all tables does match in all environment in a database. If you would like to add Top X part to your select queries, you can provide value for @TopX. If you don't want it set @TopX=0 that will exclude Top X part from Select queries. 

we are going to use Cursor and load the results in Temp table and finally select from temp table. The final query will return us Database Name, Schema Name, Table Name ,Column List and Select Query. I provided this all information so you can use for other requirements.

USE yourdbname 

go 

--Set the value of @TopX=0 if you don't want to use Top X in Select 
--If you like query with Top X, then provide value to @TopX 
DECLARE @TopX VARCHAR(10)=0 
DECLARE @SchemaName VARCHAR(100) 
DECLARE @TableName VARCHAR(100) 
DECLARE @DatabaseName VARCHAR(100) 

--Create Temp Table to Save Results   
IF Object_id('tempdb..#Results') IS NOT NULL 
  DROP TABLE #results 

CREATE TABLE #results 
  ( 
     databasename VARCHAR(100), 
     schemaname   VARCHAR(100), 
     tablename    VARCHAR(100), 
     columnlist   VARCHAR(max), 
  ) 

DECLARE cur CURSOR FOR 
  SELECT table_catalog, 
         table_schema, 
         table_name 
  FROM   information_schema.tables 
  WHERE  table_type = 'BASE TABLE' 

OPEN cur 

FETCH next FROM cur INTO @DatabaseName, @SchemaName, @TableName 

WHILE @@FETCH_STATUS = 0 
  BEGIN 
      --Get List of the Columns from Table without Identity Column   
      DECLARE @ColumnList NVARCHAR(max)=NULL 

      SELECT @ColumnList = COALESCE(@ColumnList + '],[', '') + c.NAME 
      FROM   sys.columns c 
             INNER JOIN sys.tables t 
                     ON c.object_id = t.object_id 
      WHERE  Object_name(c.object_id) = @TableName 
             AND Schema_name(schema_id) = @SchemaName 
             AND is_identity = 0 

      SET @ColumnList='[' + @ColumnList + ']' 

      DECLARE @SQL NVARCHAR(max)=NULL 

      SET @SQL= 'select  ''' + @DatabaseName + ''' AS DBName,' 
                + '''' + @SchemaName + ''' AS SchemaName,' + '''' 
                + @TableName + ''' AS TableName,' + '''' 
                + @ColumnList + ''' AS ColumnList' 

      -- PRINT @SQL   
      --Log DatabaseName,TableName,Schema Name and List of Columns in Temp Table           
      INSERT INTO #results 
      EXEC(@SQL) 

      FETCH next FROM cur INTO @DatabaseName, @SchemaName, @TableName 
  END 

CLOSE cur 

DEALLOCATE cur 

SELECT *, 
       'Select ' + CASE WHEN @TopX=0 THEN ' ' ELSE ' Top '+@TopX +' 'END + 
       columnlist + 
       ' from  [' 
       + databasename + '].[' + schemaname + '].[' 
       + tablename + ']' AS SelectQuery 
FROM   #results 
--drop table #Results   


When I ran above query on my Database, with @TopX=10. Here are my results. You can copy the SelectQuery Column Values and paste in new query and run.

How to generate Select Query with Top X for all the Tables in a database dynamically in SQL Server

Check out our other posts on Data Analysis / Data Validation / Data Cleansing

No comments:

Post a Comment