01 March 2025

💎🤖SQL Reloaded: Copilot Stories (Part VII: Using the sp_MSForEachTable Undocumented Stored Procedures in SQL Server)

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 not 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 scripts 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.*/

The code can be used to overwrite the value of a column that exists in some of the tables. One needs to be careful if there are tables which have same names but different meaning! Review the tables and their values first!

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!

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


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

As can be seen, the query uses the metadata available to retrieve the tables in scope.

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


Prompt:
Write a query that uses the sp_MSForEachTable stored procedure to count all the records for a subset of tables based on a different column from each table. 

Output:

DECLARE @sql NVARCHAR(MAX)

SET @sql = ''
EXEC sp_MSForEachTable 
    @command1 = 'IF ''?'' LIKE ''%subset%'' 
                 BEGIN 
                     IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = PARSENAME(''?'', 1) AND COLUMN_NAME = ''Column1'')
                     BEGIN
                         SET @sql = @sql + ''SELECT ''''?'''' AS TableName, COUNT(Column1) AS RecordCount FROM '' + ''?'' + ''; ''
                     END
                     ELSE IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = PARSENAME(''?'', 1) AND COLUMN_NAME = ''Column2'')
                     BEGIN
                         SET @sql = @sql + ''SELECT ''''?'''' AS TableName, COUNT(Column2) AS RecordCount FROM '' + ''?'' + ''; ''
                     END
                 END',
    @replacechar = '?',
    @command2 = 'EXEC sp_executesql @sql'

PRINT @sql

The output is not what I intended, though it looks interesting enough to give it a try...

Prompt:
Create a test table that contains a list of tables and corresponding fields from a table. There can be multiple columns available for each table. Write a query that uses the sp_MSForEachTable stored procedure to count all the distinct value for each combination of values available in the test table

Output:

-- Step 1: Create the Test Table
-- Create the test table
CREATE TABLE TestTable (
    TableName NVARCHAR(128),
    ColumnName NVARCHAR(128)
);

-- Insert sample data into the test table
INSERT INTO TestTable (TableName, ColumnName) VALUES ('Table1', 'ColumnA');
INSERT INTO TestTable (TableName, ColumnName) VALUES ('Table1', 'ColumnB');
INSERT INTO TestTable (TableName, ColumnName) VALUES ('Table2', 'ColumnC');
INSERT INTO TestTable (TableName, ColumnName) VALUES ('Table2', 'ColumnD');
INSERT INTO TestTable (TableName, ColumnName) VALUES ('Table3', 'ColumnE');

--Step 2: Query to Count Distinct Values
DECLARE @sql NVARCHAR(MAX)

SET @sql = ''
EXEC sp_MSForEachTable 
    @command1 = 'DECLARE @table NVARCHAR(128), @column NVARCHAR(128)
                 SELECT @table = TableName, @column = ColumnName
                 FROM TestTable
                 WHERE TableName = PARSENAME(''?'', 1)
                 IF @column IS NOT NULL
                 BEGIN
                     SET @sql = @sql + ''SELECT ''''?'''' AS TableName, '''' + @column + '''' AS ColumnName, COUNT(DISTINCT '' + @column + '') AS DistinctCount FROM '' + ''?'' + ''; ''
                 END',
    @replacechar = '?',
    @command2 = 'EXEC sp_executesql @sql'

PRINT @sql

Use the following code to create a table for testing the above scripts. The scripts should be run in a small, ideally empty database in a non-productive environment!

Prompt:
Create a table that contains the DataAreaId and CompanyName and populate it with 5 records.

Output:

-- Create the table
CREATE TABLE Company (
    DataAreaId INT,
    CompanyName NVARCHAR(100)
);

-- Insert 5 records into the table
INSERT INTO Company (DataAreaId, CompanyName) VALUES (1, 'Company A');
INSERT INTO Company (DataAreaId, CompanyName) VALUES (2, 'Company B');
INSERT INTO Company (DataAreaId, CompanyName) VALUES (3, 'Company C');
INSERT INTO Company (DataAreaId, CompanyName) VALUES (4, 'Company D');
INSERT INTO Company (DataAreaId, CompanyName) VALUES (5, 'Company E');

The queries should be run in SQL Server environment where the sp_MSForEachTable is available. In SQL databases the code will result in the following error:

"Msg 2812, Level 16, State 62, Line 1, Could not find stored procedure 'sp_MSForEachTable'."

Previous Post <<||>> Next Post

💎🤖💫SQL Reloaded: Copilot Stories (Part VI: Queries based on Dynamics 365 for Finance & Operations)

As part of ad-hoc queries, SSRS/Paginated Reports and Data Warehouse solutions, during the past 15 years I wrote hundreds of queries based on Dynamics AX, respectively on Dynamics 365 (D365) for Finance, Supply Chain, Production, Human Resources and a few other modules. So, given that the documentation for the D365 schema is available online, I've been wondering how easy would be to write a basic query using M365 Copilot. A relatively simple example would be the join between the Products (InventTable) and Prices (InventTableModule) tables, a quite simple thing to do (the joins were discussed in here).

Disclaimer: The below code was generated by Copilot via prompts used for demonstration purposes!

The first attempt proved to be far from the expectations:

Prompt:
Write a query that joins InventTable and InventTableModule to retrieve the Item Number, Description and the Item Prices from Dynamics 365 for Finance. Include tables' schema and rewrite the query by placing the comma first.

Output:

SELECT 
    , IT.ItemId AS [Item Number]
    , IT.ItemName AS [Description]
    , ITM.Price AS [Item Price]
FROM 
    dbo.InventTable IT
JOIN 
    dbo.InventTableModule ITM 
    ON IT.ItemId = ITM.ItemId 
    AND IT.DataAreaId = ITM.DataAreaId
WHERE 
    ITM.ModuleType = 0 -- Assuming 0 represents the sales module, adjust as necessary

A first version of the query didn't included the join on DataAreaId. Another problem is that compared with the previous versions, in D365 the ItemName is not available in the InventTable anymore while the ItemName was incorrectly considered as Description. The differences between the various versions of D365 are minimal, though the differences between D365 and AX 2009 are considerable with important changes due to redesign.

Prompt:
Rewrite the query to provide the other existing prices via a grouping.

Output:

SELECT 
    , IT.ItemId AS [Item Number]
    , IT.ItemName AS [Description]
    , STRING_AGG(CAST(ITM.Price AS NVARCHAR), ', ') AS [Item Prices]
FROM 
    dbo.InventTable IT
JOIN 
    dbo.InventTableModule ITM 
    ON IT.ItemId = ITM.ItemId 
    AND IT.DataAreaId = ITM.DataAreaId
WHERE 
    ITM.ModuleType = 0 -- Assuming 0 represents the sales module, adjust as necessary
GROUP BY 
    IT.ItemId, IT.ItemName

The generated query shows all the prices as a string, solution which might work if we change the delimiter, though is not an optimal solution at all.

Prompt:
Rewrite the query to provide the other existing individual prices via a grouping in separate fields.

Output:

SELECT 
    , IT.ItemId AS [Item Number]
    , IT.ItemName AS [Description]
    , MAX(CASE WHEN ITM.ModuleType = 0 THEN ITM.Price END) AS [Sales Price]
    , MAX(CASE WHEN ITM.ModuleType = 1 THEN ITM.Price END) AS [Purchase Price]
    , MAX(CASE WHEN ITM.ModuleType = 2 THEN ITM.Price END) AS [Cost Price]
FROM 
    dbo.InventTable IT
JOIN 
    dbo.InventTableModule ITM 
    ON IT.ItemId = ITM.ItemId 
    AND IT.DataAreaId = ITM.DataAreaId
GROUP BY 
    IT.ItemId, IT.ItemName

This version is closer to the original query I wrote some time ago:

-- Item prices via group by
SELECT ITD.DataAreaId 
, ITD.ItemId  
, ITD.InventPrice
, ITD.InventUnitId
, ITD.PurchPrice
, ITD.PurchUnitId
, ITD.SalesPrice
, ITD.SalesUnitId
FROM dbo.InventTable ITM
     LEFT JOIN (-- price details
     SELECT ITD.ItemId
     , ITD.DataAreaId 
     , Max(CASE ITD.ModuleType WHEN 0 THEN ITD.Price END) InventPrice
     , Max(CASE ITD.ModuleType WHEN 0 THEN ITD.UnitId END) InventUnitId
     , Max(CASE ITD.ModuleType WHEN 1 THEN ITD.Price END) PurchPrice
     , Max(CASE ITD.ModuleType WHEN 1 THEN ITD.UnitId END) PurchUnitId
     , Max(CASE ITD.ModuleType WHEN 2 THEN ITD.Price END) SalesPrice
     , Max(CASE ITD.ModuleType WHEN 2 THEN ITD.UnitId END) SalesUnitId
     FROM dbo.InventTableModule ITD
     GROUP BY ITD.ItemId
     , ITD.DataAreaId 
    ) ITD
       ON ITD.ItemId = ITM.ItemId
      AND ITD.DataAreaId = ITM.DataAreaId
WHERE ITD.DataAreaID = 'XYZ'
ORDER BY ITD.ItemId

Prompt:
Rewrite the query to provide the other existing individual prices via a grouping in separate fields.

Output:

SELECT 
    , IT.ItemId AS [Item Number]
    , IT.ItemName AS [Description]
    , Sales.Price AS [Sales Price]
    , Purchase.Price AS [Purchase Price]
    , Cost.Price AS [Cost Price]
FROM 
    dbo.InventTable IT
LEFT JOIN 
    dbo.InventTableModule Sales 
    ON IT.ItemId = Sales.ItemId 
    AND IT.DataAreaId = Sales.DataAreaId 
    AND Sales.ModuleType = 0 -- Assuming 0 represents the sales module
LEFT JOIN 
    dbo.InventTableModule Purchase 
    ON IT.ItemId = Purchase.ItemId 
    AND IT.DataAreaId = Purchase.DataAreaId 
    AND Purchase.ModuleType = 1 -- Assuming 1 represents the purchase module
LEFT JOIN 
    dbo.InventTableModule Cost 
    ON IT.ItemId = Cost.ItemId 
    AND IT.DataAreaId = Cost.DataAreaId 
    AND Cost.ModuleType = 2 -- Assuming 2 represents the cost module

The structure of the query looks promising, though same in the previous query Copilot got the uses for ModuleType wrong (see below)! And here's the query I wrote previously:

-- Item Prices without grouping
SELECT ITM.DataAreaId 
, ITM.ItemId 
, ILP.UnitId InventUnitId 
, ILP.Price InventPrice 
, IPP.UnitId PruchaseUnitId
, IPP.Price PurchasePrice
, ISP.UnitId SalesUnitId
, ISP.Price SalesPrice
FROM dbo.InventTable ITM
      LEFT JOIN dbo.InventTableModule ILP
        ON ITM.ItemId = ILP.ItemId
       AND ITM.DataAreaId = ILP.DataAreaId
       AND ILP.ModuleType = 0 -- Warehouse
      LEFT JOIN dbo.InventTableModule IPP
        ON ITM.ItemId = IPP.ItemId
       AND ITM.DataAreaId = IPP.DatareaId 
       AND IPP.ModuleType = 1 -- Purchases
      LEFT JOIN dbo.InventTableModule ISP
        ON ITM.ItemId = ISP.ItemId
       AND ITM.DataAreaId = ISP.DataAreaId 
       AND ISP.ModuleType = 2 -- Sales	
WHERE ITM.DataAreaId = 'XYZ'

Probably the Copilot for Dynamics 365 Finance and Operations [1] works much better than the one from M365. Unfortunately, I don't have access to it yet! Also, if I would invest more time in the prompts the results would be closer to the queries I wrote. It depends also on the goal(s) considered - build a skeleton on which to build the logic, respectively generate the final query via the prompts. Probably the 80-20 rule applies here as well. 

Frankly, for a person not knowing the D365 data model, the final queries generated are a good point to start (at least for searching for more information on the web) as long Copilot got the prompts right. It will be also interesting to see how business rules related to specific business processes (including customizations) will be handled. The future looks bright (at least for the ones still having a job in the field)!

Happy coding!

Previous Post <<||>> Next Post

References:
[1] Microsoft Learn (2024) Microsoft Learn: Overview of Copilot capabilities in finance and operations apps [link]

🏗️Software Engineering: Responsibility (Just the Quotes)

"Poor management can increase software costs more rapidly than any other factor. Particularly on large projects, each of the following mismanagement actions has often been responsible for doubling software development costs." (Barry Boehm, "Software Engineering Economics", 1981)

"A subsystem is a set of classes" (and possibly other subsystems) collaborating to fulfill a set of responsibilities. Although subsystems do not exist as the software executes, they are useful conceptual entities." (Rebecca Wirfs-Brock, "Object-oriented Design: A. responsibility-driven approach", 1989)

"Architecture is defined as a clear representation of a conceptual framework of components and their relationships at a point in time [...] a discussion of architecture must take into account different levels of architecture. These levels can be illustrated by a pyramid, with the business unit at the top and the delivery system at the base. An enterprise is composed of one or more Business Units that are responsible for a specific business area. The five levels of architecture are Business Unit, Information, Information System, Data and Delivery System. The levels are separate yet interrelated. [...] The idea if an enterprise architecture reflects an awareness that the levels are logically connected and that a depiction at one level assumes or dictates that architectures at the higher level." (W Bradford Rigdon, "Architectures and Standards", 1989)

"The data-driven approach to object-oriented design focuses on the structure of the data in a system. This results in the incorporation of structural information in the definitions of classes. Doing so violates encapsulation. The responsibility-driven approach emphasizes the encapsulation of both the structure and behavior of objects. By focusing on the contractual responsibilities of a class, the designer is able to postpone implementation considerations until the implementation phase. While responsibility-driven design is not the only technique addressing this problem, most other techniques attempt to enforce encapsulation during the implementation phase. This is too late in the software life-cycle to achieve maximum benefits." (Rebecca Wirfs-Brock, "Object-oriented design: a responsibility-driven approach", 1989)

"Programmers are responsible for software quality - quality in their own work, quality in the products that incorporate their work, and quality at the interfaces between components. Quality has never been and will never be tested in. The responsibility is both moral and professional." (Boris Beizer, "Software Testing Techniques", 1990)

"[Object-oriented analysis is] the challenge of understanding the problem domain and then the system's responsibilities in that light." (Edward Yourdon, "Object-Oriented Design", 1991) 

"Extreme Programming is a discipline of software development with values of simplicity, communication, feedback and courage. We focus on the roles of customer, manager, and programmer and accord key rights and responsibilities to those in those roles." (Ron Jeffries, "Extreme Programming Installed", 2001)

"An object-oriented application is a set of interacting objects. Each object is an implementation of one or more roles. A role supports a set of related" (cohesive) responsibilities. A responsibility is an obligation to perform a task or know certain information. And objects don't work in isolation, they collaborate with others in a community to perform the overall responsibilities of the application. So a conceptual view, at least to start, is a distillation of the key object roles and their responsibilities" (stated at a fairly high level). More than likely" (unless you form classification hierarchies and use inheritance and composition techniques) many candidates you initially model will map directly to a single class in some inheritance hierarchy. But I like to open up possibilities by think first of roles and responsibilities, and then as a second step towards a specification-level view, mapping these candidates to classes and interfaces." (Rebecca Wirfs-Brock, [interview] 2003)

"The single most important trait of a professional programmer is personal responsibility. Professional programmers take responsibility for their career, their estimates, their schedule commitments, their mistakes, and their workmanship. A professional programmer does not pass that responsibility off on others." (Robert C Martin, [in Kevlin Henney’s "97 Things Every Programmer Should Know", 2024 )

"Conceptual models are best thought of as design-tools - a way for designers to straighten out and simplify the design and match it to the users' task-domain, thereby making it clearer to users how they should think about the application. The designers' responsibility is to devise a conceptual model that seems natural to users based on the users'' familiarity with the task domain. If designers do their job well, the conceptual model will be the basis for users' mental models of the application." (Jeff Johnson & Austin Henderson, "Conceptual Models", 2011)

"The fact that bugs will certainly occur in your code does not mean you aren't responsible for them. The fact that the task to write perfect software is virtually impossible does not mean you aren't responsible for the imperfection." (Robert C Martin,"The Clean Coder: A code of conduct for professional programmers", 2011)

"Any software project must have a technical leader, who is responsible for all technical decisions made by the team and have enough authority to make them. Responsibility and authority are two mandatory components that must be present in order to make it possible to call such a person an architect." (Yegor Bugayenko, "Code Ahead", 2018)

"Attributing bugs to their authors doesn't make them more responsible, only more scared." (Yegor Bugayenko, "Code Ahead", 2018)

"A 'stream' is the continuous flow of work aligned to a business domain or organizational capability. Continuous flow requires clarity of purpose and responsibility so that multiple teams can coexist, each with their own flow of work. A stream-aligned team is a team aligned to a single, valuable stream of work; this might be a single product or service, a single set of features, a single user journey, or a single user persona." (Matthew Skelton & Manuel Pais, "Team Topologies: Organizing Business and Technology Teams for Fast Flow", 2019)

"Engineering managers have a responsibility to optimize their teams. They improve engineering workflows and reduce dependencies and repetitive tasks. Self-sustaining teams minimize dependencies that hinder them in their efforts to achieve their objectives. Scalable teams minimize software delivery steps and eliminate bottlenecks. The mechanisms to achieve this may include the use of tools, conventions, documentation, processes, or abstract things such as values and principles. Any action that produces a tangible improvement in the speed, reliability, or robustness of your team's work is worth your consideration." (Morgan Evans, "Engineering Manager's Handbook", 2023)

"In a workplace setting, accountability is the willingness to take responsibility for one's actions and their outcomes. Accountable team members take ownership of their work, admit their mistakes, and are willing to hold each other accountable as peers." (Morgan Evans, "Engineering Manager's Handbook", 2023)

"Systems architecture is the portion of any project over which the engineering team has the most control. This design is usually less of a collaboration between different functions and more clearly in the domain of engineers. As such, engineering managers have a high responsibility to own this process and its decisions. To produce the best decisions possible, you must have the right decision-building blocks: complete information to work from and a structured methodology to guide you." (Morgan Evans, "Engineering Manager's Handbook", 2023)

Related Posts Plugin for WordPress, Blogger...

About Me

My photo
Koeln, NRW, Germany
IT Professional with more than 24 years experience in IT in the area of full life-cycle of Web/Desktop/Database Applications Development, Software Engineering, Consultancy, Data Management, Data Quality, Data Migrations, Reporting, ERP implementations & support, Team/Project/IT Management, etc.