Useful Metadata Generated SQL Queries

July 8, 2021 | By Jason Bonello

On numerous occasions, we tend to utilise and re-use basic T-SQL coding to facilitate some tasks or do some checks. In most cases it would not take us long to build/type every time across the different databases, however having some of these queries in one place might make it even easier to just copy and paste off a bookmarked page.

Query generation off SQL Metadata tables

The following query generates one query that includes a Row Count query for each table in the database, using the UNION ALL command to allow one result set after the query execution.

--Getting a Count of rows from all Tables that exist in a database
SET NOCOUNT ON;
GO

SELECT 
CONCAT(
STRING_AGG(
CAST(
'SELECT '''+ QUOTENAME(S.NAME) + '.'+ QUOTENAME(O.NAME) + ''' AS TableName, COUNT(*) AS [Row_Count] FROM '+ QUOTENAME(S.NAME) + '.'+QUOTENAME(O.NAME)
 AS VARCHAR(MAX))+ CHAR(13) + CHAR(10)
, ' UNION ALL '+ CHAR(13) + CHAR(10)
),
'ORDER BY [TableName]') AS Query
FROM SYS.ALL_OBJECTS O
LEFT OUTER JOIN SYS.SCHEMAS S ON O.SCHEMA_ID = S.SCHEMA_ID
WHERE [TYPE]= 'U';

Find Columns in Objects

In some cases we need to check if a column exists within any artefact in the database, or we know it exists and we are not sure whether it exists in multiple places. The following query will allow us to get the objects that have that column name.

--Change column names in WHERE condition
SELECT DISTINCT QUOTENAME(schema_name(O.schema_id)) + '.' + QUOTENAME(O.[Name]) AS [SchemaTable_Name], C.[Name] AS [Column_Name], type_desc AS [Object_Type]
FROM sys.all_columns C
LEFT OUTER JOIN sys.all_objects O ON C.object_id = O.object_id
WHERE C.[Name] IN	(
      'ColumnName1', 
      'ColumnName2'
      )
ORDER BY [Object_Type], [SchemaTable_Name], [Column_Name]

If the exact name of the column is not known, one can utilise the LIKE function to search for part of the name, similar to the below:

--Change column names in WHERE condition 
SELECT DISTINCT QUOTENAME(schema_name(O.schema_id)) + '.' + QUOTENAME(O.[Name]) AS [SchemaTable_Name], C.[Name] AS [Column_Name], type_desc AS [Object_Type]
FROM sys.all_columns C
LEFT OUTER JOIN sys.all_objects O ON C.object_id = O.object_id
WHERE C.[Name] LIKE '%Name1%' OR C.[Name] LIKE '%Name2%'
ORDER BY [Object_Type], [SchemaTable_Name], [Column_Name]

Column Listing

This query extracts the list of columns and some relevant important information for each table within a database.

A variable named @TableName has been included in case someone needs to focus on only one table. However when left NULL/blank (as is by default), the query will run for all tables within the database, ignoring the @TableName filter.

--Generate list of columns and relevant information per table
--If columns of only one table is required, change the default blank string of the @TableName variable. Else leave as is to list all.

DECLARE @TableName VARCHAR(MAX) = NULL

IF @TableName IS NULL OR @TableName = ''
  SET @TableName = (SELECT STRING_AGG(O.[Name],',') FROM sys.all_objects O WHERE type = 'U')

SELECT 
       SCHEMA_NAME(O.[Schema_Id]) AS Schema_Name
      ,O.[Name] AS Table_Name
      ,C.[Name] AS Column_Name
      ,UPPER(T.[Name]) AS Column_Type
      ,C.[Max_Length] AS Max_Length
      ,C.[Precision] AS Precision
      ,C.[Scale] AS Scale
      ,CASE WHEN C.[Is_Nullable] = 1 THEN 'Yes' ELSE 'No' END AS [Nullable]
      ,C.[Column_Id] AS Column_Id
      ,CASE WHEN C.[Is_Identity] = 1 THEN 'Yes' ELSE 'No' END AS [Is_Identity]
      ,CASE WHEN P.[Column_Name] IS NOT NULL THEN 'Yes' ELSE 'No' END AS [Is_PrimaryKey]
      ,P.[PrimaryKey_Name]
FROM sys.all_columns C
LEFT OUTER JOIN sys.types T ON C.[System_type_id] = T.[System_type_id]
INNER JOIN sys.all_objects O ON C.[object_id] = O.[object_id] AND O.[type] = 'U' AND O.[Name] IN ( SELECT * FROM STRING_SPLIT(@TableName, ','))
LEFT OUTER JOIN
(
  SELECT 
      tbls.[schema_id] as [Schema_Id],
      pks.[name] as PrimaryKey_Name,
      idxcols.[index_column_id] as Column_Id,
      cols.[name] as Column_Name,
      tbls.[name] as Table_Name
  FROM sys.tables AS tbls
  INNER JOIN sys.indexes AS pks ON tbls.[object_id] = pks.[object_id] AND pks.[is_primary_key] = 1
  INNER JOIN sys.index_columns AS idxcols ON idxcols.[object_id] = pks.[object_id] AND idxcols.[index_id] = pks.[index_id]
  INNER JOIN sys.columns AS cols ON pks.[object_id] = cols.[object_id] AND cols.[column_id] = idxcols.[column_id]
) AS P ON P.[Schema_Id] = O.[Schema_Id] AND P.[Column_Id] = C.[Column_Id] AND P.[Column_Name] = C.[Name] AND P.[Table_Name] = O.[Name]
WHERE T.[Name] <> 'SYSNAME'
ORDER BY [Schema_Name], [Table_Name], [Column_Id], [Column_Name]

Test ETLs

Let’s assume you need to do some high-level testing to ensure that a Full Load of an ETL run from Source will provide the same results following some changes.

The following 3 step query can be used to:

Perform a backup of the tables within a Schema (for example you need to focus on the Stage schema/layer, if that is how the database is structured). If the variable is left NULL, then no filtering is applied and all tables within the database will be considered.
After running the ETL run, do any comparisons and checks like row counts (this can be extended to further checks, for example comparing MAX/MIN of Keys or SUMs etc.)
To keep the test Database clean, you can finally execute a drop table of all _BKP tables once you are satisfied with tests and saved any evidence needed outside the database.

Each step will provide a query or set of queries in SQL result window, and that query can then be copied and pasted in the query window and executed from there to perform the actual task of each step.

--Change the value from NULL to the required Schema name if filtering is needed
DECLARE @SchemaName VARCHAR(MAX) = NULL

IF @SchemaName IS NULL OR @SchemaName = ''
  SET @SchemaName = (SELECT STRING_AGG(S.[Name],',') FROM sys.schemas S)

--STEP 1: BACKUP
--Execute result of this query to take a backup of the tables under the same schema and using the same table name, suffixed by _BKP
SELECT 'SELECT * INTO ' + QUOTENAME(S.[Name]) + '.' + QUOTENAME(O.[Name]+ '_BKP') +' FROM ' + QUOTENAME(S.[Name]) + '.' + QUOTENAME(O.[Name]) + ';' AS [Backup_Queries]
FROM sys.objects O
LEFT OUTER JOIN sys.schemas S ON O.[schema_id] = S.[schema_id]
WHERE [Type] = 'U' AND S.[Name] IN (SELECT * FROM STRING_SPLIT(@SchemaName, ',')) AND RIGHT(O.[Name],4) <> '_BKP'

--STEP 2: DO CHECKS OR COMPARISONS
SELECT  
CONCAT(
STRING_AGG(
CAST(
'SELECT ''' + QUOTENAME(S.[Name]) + '.' + QUOTENAME(O.[Name]) + ''' AS [Active_Table_Name], 
(SELECT COUNT(*) FROM ' + QUOTENAME(S.[Name]) + '.' + QUOTENAME(O.[Name]) +') AS [Row_Count_ActiveTbl],
''' + QUOTENAME(S.[Name]) + '.' + QUOTENAME(O.[Name] + '_BKP')+''' AS [Backup_Table_Name], 
(SELECT COUNT(*) FROM ' + QUOTENAME(S.[Name]) + '.' + QUOTENAME(O.[Name] + '_BKP') +') AS [Row_Count_BackupTbl],
((SELECT COUNT(*) FROM ' + QUOTENAME(S.[Name]) + '.' + QUOTENAME(O.[Name]) +') - (SELECT COUNT(*) FROM ' + QUOTENAME(s.[Name]) + '.' + QUOTENAME(O.[Name] + '_BKP') +')) AS [Row_Count_Difference]'
 AS VARCHAR(MAX))+ CHAR(13) + CHAR(10)
, ' UNION ALL '+ CHAR(13) + CHAR(10)
),
'ORDER BY [Row_Count_Difference] DESC, [Active_Table_Name]'
)AS [Comparison_Queries]
FROM sys.objects O
LEFT OUTER JOIN sys.schemas S ON O.[schema_id] = S.[schema_id]
WHERE [Type] = 'U' AND S.[Name] IN (SELECT * FROM STRING_SPLIT(@SchemaName, ',')) AND RIGHT(O.[Name],4) <> '_BKP'

--STEP 3: CLEAN/DELETE BACKUP TABLES
--Only execute result of this query if intending to drop the tables (and lose the data within), after the comparison checks have been done
SELECT 'DROP TABLE ' + QUOTENAME(S.[Name]) + '.' + QUOTENAME(O.[Name]+ '_BKP')+ ';'  AS [Drop_Queries]
FROM sys.objects O
LEFT OUTER JOIN sys.schemas S ON O.[schema_id] = S.[schema_id]
WHERE [Type] = 'U' AND S.[Name] IN (SELECT * FROM STRING_SPLIT(@SchemaName, ',')) AND RIGHT(O.[Name],4) <> '_BKP'

Blocking transactions

In case any checks need to be done on a database to check whether any transactions are blocking and possibly start investigating any performance issues, this query can be used.

--Find blocking queries on Azure SQL DB
--Original source: https://azure.microsoft.com/pt-br/blog/finding-blocking-queries-in-sql-azure/

SELECT R.SESSION_ID
  ,R.PLAN_HANDLE
  ,R.[SQL_HANDLE]
  ,R.REQUEST_ID
  ,R.START_TIME
  ,R.[STATUS]
  ,R.COMMAND
  ,R.DATABASE_ID
  ,R.[USER_ID]
  ,R.WAIT_TYPE
  ,R.WAIT_TIME
  ,R.LAST_WAIT_TYPE
  ,R.WAIT_RESOURCE
  ,R.TOTAL_ELAPSED_TIME
  ,R.CPU_TIME
  ,R.TRANSACTION_ISOLATION_LEVEL
  ,R.ROW_COUNT
  ,ST.[TEXT]
FROM SYS.DM_EXEC_REQUESTS R
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(R.[SQL_HANDLE]) AS ST
WHERE R.BLOCKING_SESSION_ID = 0
  AND R.SESSION_ID IN (
    SELECT DISTINCT (BLOCKING_SESSION_ID)
    FROM SYS.DM_EXEC_REQUESTS
    )
GROUP BY R.SESSION_ID
  ,R.PLAN_HANDLE
  ,R.[SQL_HANDLE]
  ,R.REQUEST_ID
  ,R.START_TIME
  ,R.[STATUS]
  ,R.COMMAND
  ,R.DATABASE_ID
  ,R.[USER_ID]
  ,R.WAIT_TYPE
  ,R.WAIT_TIME
  ,R.LAST_WAIT_TYPE
  ,R.WAIT_RESOURCE
  ,R.TOTAL_ELAPSED_TIME
  ,R.CPU_TIME
  ,R.TRANSACTION_ISOLATION_LEVEL
  ,R.ROW_COUNT
  ,ST.[TEXT]
ORDER BY R.TOTAL_ELAPSED_TIME DESC

It is worth noting that there might be different ways of obtaining the same results via other different queries. These provided queries are just some examples.

Get in touch to find out how we can help you with your data, get in touch.