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
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
- How to Search in all Columns for all tables in a database for Date Value in SQL Server
- How to Find Percentage of Null Values in every Column of all the Tables in SQL Server Database
- How to Find Percentage of Empty or Blank Values in every Column of all the Tables in SQL Server Database
- How to Get Row Count Of All The Tables In SQL Server Database
- How to search for a String in all Columns in all tables in SQL Server Database
- How to find and Replace Text in all the columns in all the tables in SQL server Database
- How to find values with Leading or Trailing Spaces in SQL Server Table
- How to Trim Leading and Trailing spaces for all the values in all the Columns in all the Tables in a SQL Server Database
- How to Find Duplicate Records in All the Tables in SQL Server Database
- How to Find & Delete Duplicate Records on All Tables in SQL Server Database
- Concatenate SQL Server Columns for each Table Query Dynamically in SQL Server
- How to List all Missing Identity Values for all Tables in SQL Server Database
- How to Find Max and Min Values for all the columns for all the tables in SQL Server Database
No comments:
Post a Comment