Probably few know that SQL Server makes available a set of undocumented stored
procedures that can be useful in certain scenarios that don't involve code
that will deployed into production. One of them is sp_MSForEachTable on
which I wrote several
posts until now. The stored procedure executes one or more scripts based on a
set of constraints against the subset of available tables. The first
surprising fact is that Copilot does know the respective stored procedure and
seems to correctly generate code for it!
Disclaimer: The below code was generated by Copilot via prompts used for
demonstration purposes!
Please note that the following code is provided as it is and you can use it
on your own risk! Don't run the code in a productive environment and make
sure that you have a usable backup! Please consider all caution when you use
a feature marked as undocumented!
Let's start with a simple script and increase the complexity of the queries as
it goes!
Prompt:
Write a query that uses the sp_MSForEachTable stored procedure in SQL
Server
Output:
-- query that uses this stored procedure to update a specific column in all tables:
EXEC sp_MSForEachTable 'UPDATE ? SET YourColumn = ''NewValue'' WHERE YourCondition'
/*
? is a placeholder for the table name.
YourColumn is the column you want to update.
NewValue is the new value you want to set.
YourCondition is the condition that must be met for the update to occur.*/
As the description says, "?" acts as placeholder for table's name while the
other values need to be provided explicitly. The code can be used to overwrite
the value of a column that exists in some of the tables. For example, when the
data come over an interface from various system, it may happen that there is a
mix of upper and lower case values that designate the same value. This might
not be an issue for Dynamics AX but can create issues further on.
Here's the actual code for performing the update:
-- update DataAreaId for DMEMF company
EXEC sp_MSForEachTable 'UPDATE ? SET DataAreaId = Upper(DataAreaId) WHERE DataAreaId = ''demf'''
Executing the code blindly can create serious damage, so always test first the
code in a test environment and have a backup available! One needs to be
careful if there are fields which have same name(s) but different meaning!
It's important to perform a thorough analysis in this direction.
Prompt:
Write a query that uses the sp_MSForEachTable stored procedure to
select records from a set of tables.
Output:
-- return all columns
EXEC sp_MSForEachTable 'SELECT * FROM ?'
-- return specific columns based on condition
EXEC sp_MSForEachTable 'SELECT Column1, Column2 FROM ? WHERE YourCondition'
The second query will result in errors if the columns don't exist, though the
execution runs further! The errors can be filtered out in Excel by sorting
them accordingly.
The query works in general for combinations of columns
available in many tables. It's the case of DataAreaId and RecId available in
Dynamics 365 for Finance and Operations for a high percentage of the tables.
RecId should be available for all the tables.
Prompt:Write a query that uses the sp_MSForEachTable stored procedure to count
all the records from a table
Output:
-- retrieving the number of records for all tables
EXEC sp_MSForEachTable 'SELECT ''?'' AS TableName, COUNT(*) AS RecordCount FROM ?'
Prompt:
Write a query that uses the sp_MSForEachTable stored procedure to
select all the records from tables containing the word invent.
Output:
DECLARE @sql NVARCHAR(MAX)
SET @sql = ''
EXEC sp_MSForEachTable
@command1 = 'IF ''?'' LIKE ''%invent%'' BEGIN SET @sql = @sql + ''SELECT * FROM '' + ''?'' + ''; '' END',
@replacechar = '?',
@command2 = 'EXEC sp_executesql @sql'
PRINT @sql
Here's the working version of the query:
-- retrieve the records for tables having 'invent' intheir name
EXEC sp_MSForEachTable
@command1 = 'DECLARE @sql nvarchar(max) = ''''
IF ''?'' LIKE ''%invent%'' BEGIN SET @sql = @sql + ''SELECT * FROM '' + ''?'' + ''; '' END;
EXEC sp_executesql @sql;'
A few days later, using the same prompt in Edge I got the following answer,
simpler and correct:
-- second version run on Edge Copilot
EXEC sp_MSForEachTable
@command1 = 'IF ''?'' LIKE ''%invent%''
BEGIN
PRINT ''?''
EXEC(''SELECT * FROM ?'')
END'
Prompt:
Write a query that uses the sp_MSForEachTable stored procedure when the
DataAreaId field exists in the table.
Output:
DECLARE @sql NVARCHAR(MAX)
SET @sql = ''
EXEC sp_MSForEachTable
@command1 = 'IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = PARSENAME(''?'', 1) AND COLUMN_NAME = ''DataAreaId'')
BEGIN
SET @sql = @sql + ''SELECT * FROM '' + ''?'' + ''; ''
END',
@replacechar = '?',
@command2 = 'EXEC sp_executesql @sql'
PRINT @sql
The query uses the metadata available to retrieve the tables in scope.
EXEC sp_MSForEachTable
@command1 = ' DECLARE @sql nvarchar(max) = ''''
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = PARSENAME(''?'', 1) AND COLUMN_NAME = ''DataAreaId'')
BEGIN
SET @sql = @sql + ''SELECT * FROM '' + ''?'' + ''; ''
EXEC sp_executesql @sql
END'
Here's the answer in Edge:
EXEC sp_MSForEachTable
@command1 = 'IF EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(''?'' ) AND name = ''DataAreaId'')
BEGIN
PRINT ''?''
EXEC(''SELECT * FROM ?'')
END'
Prompt:
Write a query that uses the sp_MSForEachTable stored procedure to
select records from a set of tables while it checks whether the fields exist
in the table.
Output:
DECLARE @sql NVARCHAR(MAX)
SET @sql = ''
EXEC sp_MSForEachTable
@command1 = 'DECLARE @table NVARCHAR(128)
SET @table = PARSENAME(''?'', 1)
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table AND COLUMN_NAME = ''Field1'')
AND EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table AND COLUMN_NAME = ''Field2'')
BEGIN
SET @sql = @sql + ''SELECT Field1, Field2 FROM '' + ''?'' + ''; ''
END',
@replacechar = '?',
@command2 = 'EXEC sp_executesql @sql'
PRINT @sql
The query used the metadata available in the
INFORMATION_SCHHEMA.COLUMNS. Unfortunately, the code doesn't work as expected. The second command is not
necessary for performing the task, so the code can be written as follows, and
now it works:
EXEC sp_MSForEachTable
@command1 = 'DECLARE @table NVARCHAR(128), @sql nvarchar(max) = ''''
SET @table = PARSENAME(''?'', 1)
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table AND COLUMN_NAME = ''DataAreaId'')
AND EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table AND COLUMN_NAME = ''ItemId'')
BEGIN
SET @sql = @sql + ''SELECT DataAreaId, ItemId FROM '' + ''?'' + ''; ''
END
EXEC sp_executesql @sql'
The query is easy to modify to include more columns after the same pattern, if
such tables exist after all (e.g. in Dynamics 365 one can search for the
combination ItemId, DataAreaId and one or more Inventory dimension like
InventSiteId, InventLocationId, etc.).
Prompt:
Write a query that uses the sp_MSForEachTable stored procedure to count
all the records for a subset of tables.
Output:
DECLARE @sql NVARCHAR(MAX)
SET @sql = ''
EXEC sp_MSForEachTable
@command1 = 'IF ''?'' LIKE ''%subset%''
BEGIN
SET @sql = @sql + ''SELECT ''''?'''' AS TableName, COUNT(*) AS RecordCount FROM '' + ''?'' + ''; ''
END',
@replacechar = '?',
@command2 = 'EXEC sp_executesql @sql'
PRINT @sql
Unfortunately, that's not what I was expecting. So, let's give it a second
try:
Prompt:
Write a query that uses the sp_MSForEachTable stored procedure to count
all the records for a subset of the tables available in the SYS.TABLES.
Output:
EXEC sp_MSForEachTable
@command1 = 'IF OBJECT_ID(''?'' , ''U'') IN (SELECT OBJECT_ID FROM SYS.TABLES WHERE NAME IN (''YourTable1'', ''YourTable2'', ''YourTable3''))
BEGIN
PRINT ''?''
EXEC(''SELECT COUNT(*) FROM ?'')
END'
Replacing the tables names accordingly, the query becomes
EXEC sp_MSForEachTable
@command1 = 'IF OBJECT_ID(''?'' , ''U'') IN (SELECT OBJECT_ID FROM SYS.TABLES WHERE NAME IN (''InventTable'', ''InventDim'', ''InventDimCombination''))
BEGIN
PRINT ''?''
EXEC(''SELECT COUNT(*) FROM ?'')
END'
Comments:
1) Edge Copilot returned the expected solution directly
and I wonder what the reason could be? It can be the fact that I run the tests
on different machines at different points in time, though more likely the
reason is that I used two different Copilots - from Edge, respectively
Microsoft 365. The expectation was to get better answers in M365 Copilot,
which wasn't the case in the above tests!
The two Copilots have different architectures and were trained with different
sets of data, respectively have different sources. One important difference is
that Edge Copilot primarily relies on web content and users' interactions
within the browser, while M365 Copilot relies on data from the D365 services.
Both use LLM and NLP, while M365 Copilot uses Microsoft Graph and Syntex.
One thing I remarked during the tests is that sometimes I get different
answers to the same question also within the same Copilot. However, that's
expected given that there are multiple ways of addressing the same problem! It
would still be interesting to understand how a solution is chosen.
2) I seldom used sp_MSForEachTable with more than one parameter, preferring to
prepare the needed statement beforehand, as in the working solutions above.
Happy coding!
Refer4ences:
[1] SQLShack (2017) An
introduction to sp_MSforeachtable; run commands iteratively through all tables
in a database [link]
Acronyms:
LLM - large language model
M365 - Microsoft 365
NLP - natural
language processing
Previous Post
<<||>>
Next Post
