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

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.

USEyourdbname

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 @TopXDECLARE@TopXVARCHAR(10)=0DECLARE@SchemaNameVARCHAR(100)DECLARE@TableNameVARCHAR(100)DECLARE@DatabaseNameVARCHAR(100)