Dynamic SQL: EXECUTESQL
EXECUTESQL is a SQL command that also can execute dynamic SQL. This is very similar to EXEC() with an added benefit. Using EXECUTESQL can save the output of a variable, for instance the Count (*) of a table and the variable can then be used later within the script. Below is an example of using a cursor to find the counts of all tables in the “HumanResources” schema.
/*Declare variables needed within the script*/
DECLARE @TableSchema AS NVARCHAR(100)
DECLARE @TableName AS NVARCHAR(100)
DECLARE @Counter AS INT
DECLARE @Count AS INT
DECLARE @SQL AS NVARCHAR(MAX)
DECLARE @ParmDefinition AS NVARCHAR(MAX)
/*Set @Counter variable to 0 before the loop kicks off.*/
SET @Counter = 0
/*Print out a header before the loop starts.*/
PRINT 'Table_Schema Table_Name Count'
/*Declare the cursor for the loop*/
DECLARE CURSOR_NAME CURSOR FOR
/*Select the data to cursor through*/
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'HumanResources'
OPEN CURSOR_NAME
FETCH NEXT FROM CURSOR_NAME INTO @TableSchema, @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
/*Set the @SQL variable to the dynamic SQL that will get executed.*/
SET @SQL = 'SET @Count_Output = (SELECT COUNT(*) AS CNT
FROM [' + @TableSchema + '].[' + @TableName + '])'
/*If using ExecuteSQL, need to declare the variable within the dynamic SQL.*/
SET @ParmDefinition = N'@Count_Output INT OUTPUT'
/*Execute the dynamic SQL, passing in @SQL, @ParmDefinition = Any variables that are used within @SQL, and how do the variables map from within @SQL to how they will be called outside of @SQL.*/
EXEC SP_EXECUTESQL @SQL, @ParmDefinition, @Count_Output = @Count OUTPUT
/*Print out the TableSchema, TableName, and the Count. This is a good way to quickly get counts and can copy and paste results to excel. In between each variable is a tab.*/
PRINT @TableSchema + ' ' + @TableName + ' ' + CONVERT(VARCHAR(10), @Count )
/*Increase @Counter by 1*/
SET @Counter = @Counter + 1
END
CLOSE CURSOR_NAME
DEALLOCATE CURSOR_NAME
Printed out Results
Copy printed results and paste to Excel