💎💫SQL Reloaded: Schema Differences between Database Versions - Part I: INFORMATION_SCHEMA version

During data migrations and other similar activities it's important to check what changed in the database at the various levels. Usually, it's useful to check when schemas, object names or table definitions changed, even if the changes are thoroughly documented. One can write a script to point out all the differences in one output, though it's recommended to check the differences at each level of detail. 

For this purpose one can use the INFORMATION_SCHEMA available for many of the RDBMS implementing it. This allows to easily port the scripts between platforms. The below queries were run on SQL Server 2025 in combination with Dynamics 365 schemas, though they should run on the earlier versions, incl. (Azure) SQL Databases. 

Such comparisons must be done from the both sides, this implying a FULL OUTER JOIN when writing a single SELECT statement, however the results can become easily hard to read and even interpret when the number of columns in output increases. Therefore, it's recommended to keep the number of columns at a minimum while addressing the scope, respectively break the FULL OUTER JOIN in two LEFT JOINs.

The simplest check is at schema level, and this can be easily done from both sides (note that database names needed to be replaced accordingly):

-- difference schemas (objects not available in the new schema)
SELECT *
FROM ( -- comparison
	SELECT DB1.CATALOG_NAME
	, DB1.SCHEMA_NAME
	, DB1.SCHEMA_OWNER
	, DB1.DEFAULT_CHARACTER_SET_NAME
	, DB2.SCHEMA_OWNER NEW_SCHEMA_OWNER
	, DB2.DEFAULT_CHARACTER_SET_NAME NEW_DEFAULT_CHARACTER_SET_NAME
	, CASE 
		WHEN DB2.SCHEMA_NAME IS NULL THEN 'schema only in old db'
		WHEN DB1.SCHEMA_OWNER <> IsNull(DB2.SCHEMA_OWNER, '') THEN 'different table type'
	  END Comment
        , CASE WHEN DB1.DEFAULT_CHARACTER_SET_NAME <> DB2.DEFAULT_CHARACTER_SET_NAME THEN 'different character sets' END Character_sets
	FROM [old database_name].INFORMATION_SCHEMA.SCHEMATA DB1

	     LEFT JOIN [new database name].INFORMATION_SCHEMA.SCHEMATA DB2

	       ON DB1.SCHEMA_NAME = DB2.SCHEMA_NAME
 ) DAT
WHERE DAT.Comment IS NOT NULL
ORDER BY DAT.CATALOG_NAME
, DAT.SCHEMA_NAME


-- difference schemas (new objects)
SELECT *
FROM ( -- comparison
	SELECT DB1.CATALOG_NAME
	, DB1.SCHEMA_NAME
	, DB1.SCHEMA_OWNER
	, DB1.DEFAULT_CHARACTER_SET_NAME
	, DB2.SCHEMA_OWNER OLD_SCHEMA_OWNER
	, DB2.DEFAULT_CHARACTER_SET_NAME OLD_DEFAULT_CHARACTER_SET_NAME
	, CASE 
		WHEN DB2.SCHEMA_NAME IS NULL THEN 'schema only in old db'
		WHEN DB1.SCHEMA_OWNER <> IsNull(DB2.SCHEMA_OWNER, '') THEN 'different table type'
	  END Comment
        , CASE WHEN DB1.DEFAULT_CHARACTER_SET_NAME <> DB2.DEFAULT_CHARACTER_SET_NAME THEN 'different character sets' END Character_sets
	FROM [new database name].INFORMATION_SCHEMA.SCHEMATA DB1
	     LEFT JOIN [old database name].INFORMATION_SCHEMA.SCHEMATA DB2
	       ON DB1.SCHEMA_NAME = DB2.SCHEMA_NAME
 ) DAT
WHERE DAT.Comment IS NOT NULL
ORDER BY DAT.CATALOG_NAME
, DAT.SCHEMA_NAME

Comments:
1) The two queries can be easily combined via a UNION ALL, though it might be a good idea then to add a column to indicate the direction of the comparison. 

The next step would be to check which objects has been changed:

-- table-based objects only in the old schema (tables & views)
SELECT *
FROM ( -- comparison
	SELECT DB1.TABLE_CATALOG
	, DB1.TABLE_SCHEMA
	, DB1.TABLE_NAME
	, DB1.TABLE_TYPE
	, DB2.TABLE_CATALOG NEW_TABLE_CATALOG
	, DB2.TABLE_TYPE NEW_TABLE_TYPE
	, CASE 
		WHEN DB2.TABLE_NAME IS NULL THEN 'objects only in old db'
		WHEN DB1.TABLE_TYPE <> IsNull(DB2.TABLE_TYPE, '') THEN 'different table type'
		--WHEN DB1.TABLE_CATALOG <> IsNull(DB2.TABLE_CATALOG, '') THEN 'different table catalog'
	  END Comment
	FROM [old database name].INFORMATION_SCHEMA.TABLES DB1
	    LEFT JOIN [new database name].INFORMATION_SCHEMA.TABLES DB2
	      ON DB1.TABLE_SCHEMA = DB2.TABLE_SCHEMA
	     AND DB1.TABLE_NAME = DB2.TABLE_NAME
 ) DAT
WHERE DAT.Comment IS NOT NULL
ORDER BY DAT.TABLE_SCHEMA
, DAT.TABLE_NAME

Comments:
1) If the database was imported under another name, then the TABLE_CATALOG will have different values as well.

At column level, the query increases in complexity, given the many aspects that must be considered:

-- difference columns (columns not available in the new scheam, respectively changes in definitions)
SELECT *
FROM ( -- comparison
	SELECT DB1.TABLE_CATALOG
	, DB1.TABLE_SCHEMA
	, DB1.TABLE_NAME
	, DB1.COLUMN_NAME 
	, DB2.TABLE_CATALOG NEW_TABLE_CATALOG
	, CASE WHEN DB2.TABLE_NAME IS NULL THEN 'column only in old db' END Comment
	, DB1.DATA_TYPE
	, DB2.DATA_TYPE NEW_DATA_TYPE
	, CASE WHEN DB2.TABLE_NAME IS NOT NULL AND IsNull(DB1.DATA_TYPE, '') <> IsNull(DB2.DATA_TYPE, '') THEN 'Yes' END Different_data_type
	, DB1.CHARACTER_MAXIMUM_LENGTH
	, DB2.CHARACTER_MAXIMUM_LENGTH NEW_CHARACTER_MAXIMUM_LENGTH
	, CASE WHEN DB2.TABLE_NAME IS NOT NULL AND IsNull(DB1.CHARACTER_MAXIMUM_LENGTH, '') <> IsNull(DB2.CHARACTER_MAXIMUM_LENGTH, '') THEN 'Yes' END Different_maximum_length
	, DB1.NUMERIC_PRECISION
	, DB2.NUMERIC_PRECISION NEW_NUMERIC_PRECISION
	, CASE WHEN DB2.TABLE_NAME IS NOT NULL AND IsNull(DB1.NUMERIC_PRECISION, '') <> IsNull(DB2.NUMERIC_PRECISION, '') THEN 'Yes' END Different_numeric_precision
	, DB1.NUMERIC_SCALE
	, DB2.NUMERIC_SCALE NEW_NUMERIC_SCALE
	, CASE WHEN DB2.TABLE_NAME IS NOT NULL AND IsNull(DB1.NUMERIC_SCALE, '') <> IsNull(DB2.NUMERIC_SCALE,'') THEN 'Yes' END Different_numeric_scale
	, DB1.CHARACTER_SET_NAME
	, DB2.CHARACTER_SET_NAME NEW_CHARACTER_SET_NAME
	, CASE WHEN DB2.TABLE_NAME IS NOT NULL AND IsNull(DB1.CHARACTER_SET_NAME, '') <> IsNull(DB2.CHARACTER_SET_NAME, '') THEN 'Yes' END Different_character_set_name 
	, DB1.COLLATION_NAME
	, DB2.COLLATION_NAME NEW_COLLATION_NAME
	, CASE WHEN DB2.TABLE_NAME IS NOT NULL AND IsNull(DB1.COLLATION_NAME, '') <> IsNull(DB2.COLLATION_NAME, '') THEN 'Yes' END Different_collation_name
	, DB1.ORDINAL_POSITION
	, DB2.ORDINAL_POSITION NEW_ORDINAL_POSITION
	, DB1.COLUMN_DEFAULT
	, DB2.COLUMN_DEFAULT NEW_COLUMN_DEFAULT
	, DB1.IS_NULLABLE
	, DB2.IS_NULLABLE NEW_IS_NULLABLE
	FROM [old database name].INFORMATION_SCHEMA.COLUMNS DB1
	    LEFT JOIN [new database name].INFORMATION_SCHEMA.COLUMNS DB2

	      ON DB1.TABLE_SCHEMA = DB2.TABLE_SCHEMA
	     AND DB1.TABLE_NAME = DB2.TABLE_NAME
	     AND DB1.COLUMN_NAME = DB2.COLUMN_NAME
 ) DAT
WHERE DAT.Comment IS NOT NULL
  OR IsNull(DAT.Different_data_type,'') = 'Yes'
  OR IsNull(DAT.Different_maximum_length,'') = 'Yes'
  OR IsNull(DAT.Different_numeric_precision,'') = 'Yes'
  OR IsNull(DAT.Different_numeric_scale,'') = 'Yes'
  OR IsNull(DAT.Different_character_set_name,'') = 'Yes'
  OR IsNull(DAT.Different_collation_name,'') = 'Yes'
ORDER BY DAT.TABLE_SCHEMA
, DAT.TABLE_NAME
, DAT.COLLATION_NAME

Comments:
1) The query targets only the most common scenarios, therefore must be changed to handle further cases (e.g. different column defaults, different attributes like nullable, etc.)!
2) The other perspective can be obtained by inverting the table names (without aliases) and changing the name of the columns from "NEW_' to "OLD_" (see the queries for schemas).
3) One can move the column-based conditions for the differences in the main query, though then is needed to duplicate the logic, which will make the code more challenging to change and debug. 

Happy coding!

💠🛠️🗒️SQL Server: Schemas [Notes]

Disclaimer: This is work in progress based on notes gathered over the years, intended to consolidate information from the various sources. 

Last updated: 15-Mar-2024

[SQL Server 2005] Schemas

• {def} a collection of database objects that are owned by a single user and form a single namespace
• a named container for database objects
• allows to group objects into separate namespaces
• collection of like objects which provide maintenance and security to those objects as a whole, without affecting objects within other schemas [1]
• reside within databases
• fulfilling a common purpose [1]
• each schema can contain zero or more data structures (aka objects) [1]
• all objects within a schema share 
• a common naming context
• a common security context [10]
• behavior of schema changed 
• ⇐ compared to SQL Server 2000
• schemas are no longer equivalent to database users
• each schema is a distinct namespace that exists independently of the database user who created it
• used as a prefix to the object name
• schema is simply a container of objects [3]
• code written for earlier releases of SQL Server may return incorrect results, if the code assumes that schemas are equivalent to database users [3]
• can be owned by any database principal
• this includes roles and application roles  [3]
• its ownership is transferable [3]
• every object is contained by a schema [6]
• anything contained by it has the same owner [6]
• separation of ownership [3]
• ownership of schemas and schema-scoped securables is transferable [3]
• objects can be moved between schemas [3]
• a single schema can contain objects owned by multiple database users  [3]
• multiple database users can share a single default schema  [3]
• permissions on schemas and schema-contained securables can be managed with greater precision than in earlier releases  [3]
• each user has a default schema [3]
• user’s default schema is used for name resolution during object creation or object reference [7]
•  {warning} a user might not have permission to create objects in the dbo schema, even if that is the user’s default schema [7]
• when a login in the sysadmin role creates an object with a single part name, the schema is always dbo [7]
• a database user can be dropped without dropping objects in a corresponding schema  [3]
• catalog views designed for earlier releases of SQL Server may return incorrect results
• ⇐ includes sysobjects
• more than 250 new catalog views were introduced to reflect the changes
•  when creating a database object, if you specify a valid domain principal (user or group) as the object owner, the domain principal will be added to the database as a schema. The new schema will be owned by that domain principal [3]
• schema-qualified object name (aka two-part object name)
• if schema is omitted, a schema resolution is performed (aka implicit resolution)
• checks whether the object exists in the user's default schema
• if it doesn't, checks whether it exists in the dbo schema [5]
• extra costs are involved in resolving the object name (aka name resolution) [5]
• uses a spinlock [8]
• in rare occasions a spinlock could not be acquired immediately on such an operation
• this may occur on a system under significant load [8]
• the contention appears on the SOS_CACHESTORE spinlock type [8]
• {resolution} ensure that you always fully qualify your table names [8]
• if multiple objects with the same name exist in different schemas, the wrong object might be retrieved [5]
• improves readability
• {recommendation} always use two-part object names in queries (aka schema-qualify objects) 
• {poor practice} partition data and objects by using only schemas 
• ⇐ instead of creating multiple databases [1]
• {poor practice} complex schemas
• developing a row-based security schema for an entire database using dozens or hundreds of views can create maintenance issues [6]
• {benefit} simplify database object management
• groups of tables can be managed from a single point [4]
• by creation of categories of tables [4]
• helps navigation through database [4]
• allow control permissions at schema level 
• {benefit} provide separation of ownership 
• allows to manage user permissions at the schema level, and then enhance them or override them at the object level as appropriate [10]
• {recommendation} manage database object security by using ownership and permissions at the schema level [2]
• {recommendation} have distinct owners for schemas or use a user without a login as a schema owner [2]
• {recommendation} not all schemas should be owned by dbo [2]
• {recommendation} minimize the number of owners for each schema [2]
• {benefit} enhance security
• by minimizing the risk of SQL injection
• by assigning objects to schema it is possible to drop users without rewriting your applications as the name resolution is no longer depend upon the user or principals names 
• used as an extra hierarchical layer for solution and security management [1]
• gives architects and developers the ability to choose between the types of logical separation of objects they have created, as well as benefit from having a combination of multiple databases and multiple schemas within them [1]
• {type} system schemas
• can't be dropped
• [default schema] dbo
• included in each database 
• if an application needs to  create objects in the under the dbo schema then by granting dbo privileges to the application [12]
• increases the attack surface of the application [12]
• increases the severity if the application is vulnerable to SQL Injection attacks [12]
• can be set and changed by using DEFAULT_SCHEMA option of [3]
• e.g. CREATE USER <user_name> WITH DEFAULT_SCHEMA = <schema_name>
• e.g. ALTER USER <user_name> WITH DEFAULT_SCHEMA = <schema_name>
• if DEFAULT_SCHEMA is left undefined, the database user will have dbo as its default schema [3]
• [SQL Server 2005] Windows Groups are not allowed to have this property [11]
• [SQL Server 2012] Windows Groups can also have a defined default schema [1]
• streamlines the process of creating users
• if no default schema is specified for a new user, instead is used the default schema of a group where the user is a member [9]
• {warning} not to be confused with the dbo role [6]
• INFORMATION_SCHEMA schema
• an internal, system table-independent view of the SQL Server metadata
• enable applications to work correctly although significant changes have been made to the underlying system tables
• guest schema
• 
  
• sys schema 
• provides a way to access all the system tables and views [7]
• {type} user-defined schemas
• {best practice} assign objects to user-defined schemas
• leaving everything in the dbo schema is like putting everything in the root directory of your hard drive [8]
• it saves the Query Processor a step from having to resolve the schema name out by itself [8]
• avoid ambiguity
• {best practice} assign each user a default schema
•  ensures that if they create an object without specifying a schema, it will automatically go into their assigned container [8]
• {type} role-based schemas
• [SQL Server 2012] every fixed database role has a schema of the same name [7]
• {exception} public role5
• {action} create objects in schema
• {prerequisite}
• schema must exist
• the user creating the object must have permission to create the object, either directly or through role membership [7]
• the user creating the object must  either [7]
• be the owner of the schema 
• be a member of the role that owns the schema
• have ALTER rights on the schema 
• have the ALTER ANY SCHEMA permission in the database
• {recommendation} group like objects together into the same schema [2]
• {operation} create schema
• {recommendation} use two-part names for database object creation and access [2]
• {operation} change schema (aka modify schema)
• when applying schema changes to an object and try to manipulate the object data in the same batch, SQL Server may not be aware of the schema changes yet and fail the data manipulation statement with a resolution error [5]
• the parsing does not check any object names or schemas because a schema may change by the time the statement executes [6]
• triggers a database lock
• invalidates existing query plans
• a new plan will need to be recompiled for the queries as soon as they are run anew
• not allowed on
• [SQL Server 2014] [memory-optimized tables]
• [table variables]
• {best practice} explicitly list column names in statements in case a schema changes 
• {operation} schema dropping
• [windows groups]
• an exception in the SQL Server security model [11]
• a secondary identity with additional capabilities that are traditionally reserved only for primary identities [11]
• require handling not seen in any other security system [11]
•  can simplify management but due to their hybrid nature, they come with some restrictions [11]
• {recommendation} for users mapped to Windows groups, try and limit each Windows user to one Windows group that has database access [2]

Previous Post <<||>> Next Post

References:

[1] 40074A: Microsoft SQL Server 2014 for Oracle DBAs, Microsoft, 2015 

[2] Bob Beauchemin et al (2012) SQL Server 2012 Security Best Practices - Operational and Administrative Tasks [whitepaper]

[3] MSDN (2005)User-Schema Separation [link]

[4] Solid Quality Learning (2007) Microsoft SQL Server 2005: Database Essentials Step by Step

[5] Itzik Ben-Gan (2008) Microsoft® SQL Server® 2008 T-SQL Fundamentals

[6] Adam Jorgensen et al (2012) Microsoft® SQL Server® 2012 Bible

[7] Kalen Delaney et al (2013) Microsoft SQL Server 2012 Internals

[8] Buck Woody (2009) SQL Server Best Practices: User-Defined Schemas [link obsolet]

[9] Microsoft (2014) 10977B: Updating Your SQL Server Skills to SQL Server 2014 (Trainer Handbook)

[10] Microsoft (2012) 20465B Designing Database Solutions for SQL Server 2012 (Trainer Handbook

[11] Laurentiu Cristofor (2008) SQL Server: Windows Groups, default schemas, and other properties [link]

[12] Dan Sellers's WebLog (2006) Post Webcast’s Notes: Securing SQL Server 2005 for Developers  [link]

[13] Microsoft Learn (2024) SQL Server 2022: System Information Schema Views (Transact-SQL)

[14] 

Acronyms:
SQL - Structured Query Language

💎🤖SQL Reloaded: Copilot Stories (Part II: Create a Schema for Customer Addresses)

Since the first post on a related topic, Copilot became available in Microsoft 365, and this opens the door for prompt engineering experiments. A prompt is a "natural language instruction that tells a large language model (LLM) to perform a task" [1], process also known as instruction tuning. Prompt engineering is thus "the process of creating and refining the prompt used by the model" [1].

For example, one can use Prompt Engineering to learn how to perform certain tasks in a programming language like SQL: learning how to define a table, write a query and database objects, etc. In a first experiment I created a set of prompts for creating a set of objects to store and perform CRUD (Create, Read, Update, Delete) operations. The experiment focused on Customer Addresses, Countries, Regions. Let's look at the prompts and their outputs. The following prompts depict the attempts in the order in which they were considered, so feel free to experiment!

Before Copilot, I used macros in Excel or stored procedures to generate similar code. which allowed me to reduce considerably the development time.

Prompt:
Give me the definition of a SQL Server stored procedure for updating the Customers from the dbo schema based on input parameters. 

Output:

CREATE PROCEDURE UpdateCustomer
    @CustomerID INT,
    @CustomerName NVARCHAR(100),
    @ContactName NVARCHAR(100),
    @Country NVARCHAR(50)
AS
BEGIN
    UPDATE dbo.Customers
    SET 
        CustomerName = @CustomerName,
        ContactName = @ContactName,
        Country = @Country
    WHERE 
        CustomerID = @CustomerID;
END;

Prompt:
Give me the definition of a SQL Server stored procedure for inserting Customers in the dbo schema based on input parameters.

Output:

CREATE PROCEDURE InsertCustomer
    @CustomerName NVARCHAR(100),
    @ContactName NVARCHAR(100),
    @Country NVARCHAR(50)
AS
BEGIN
    INSERT INTO dbo.Customers (CustomerName, ContactName, Country)
    VALUES (@CustomerName, @ContactName, @Country);
END;

Prompt:
Give me the definition of a SQL Server stored procedure for deleting a Customers from the dbo schema based on Customer Id.

Output:

CREATE PROCEDURE DeleteCustomer
    @CustomerID INT
AS
BEGIN
    DELETE FROM dbo.Customers
    WHERE CustomerID = @CustomerID;
END;

Prompt:
Give me the definition of the SQL Server table used for the above examples.

Output:

CREATE TABLE dbo.Customers (
    CustomerID INT IDENTITY(1,1) PRIMARY KEY,
    CustomerName NVARCHAR(100) NOT NULL,
    ContactName NVARCHAR(100),
    Country NVARCHAR(50)
);

Prompt:
Extend the definition of the previous table with Address information. 

Output:

CREATE TABLE dbo.Customers (
    CustomerID INT IDENTITY(1,1) PRIMARY KEY,
    CustomerName NVARCHAR(100) NOT NULL,
    ContactName NVARCHAR(100),
    Country NVARCHAR(50),
    AddressLine1 NVARCHAR(200),
    AddressLine2 NVARCHAR(200),
    City NVARCHAR(100),
    State NVARCHAR(100),
    PostalCode NVARCHAR(20)
);

Prompt:
Remove the Address information from the above table into a separate Addresses table with the Customer Id as foreign key.

Output:

-- Modified Customers Table:
CREATE TABLE dbo.Customers (
    CustomerID INT IDENTITY(1,1) PRIMARY KEY,
    CustomerName NVARCHAR(100) NOT NULL,
    ContactName NVARCHAR(100),
    Country NVARCHAR(50)
);

-- New Addresses Table:
CREATE TABLE dbo.Addresses (
    AddressID INT IDENTITY(1,1) PRIMARY KEY,
    CustomerID INT,
    AddressLine1 NVARCHAR(200),
    AddressLine2 NVARCHAR(200),
    City NVARCHAR(100),
    State NVARCHAR(100),
    PostalCode NVARCHAR(20),
    FOREIGN KEY (CustomerID) REFERENCES dbo.Customers(CustomerID)
);

Prompt:
Create a Countries table that can be referenced from the Addresses table with Country ISO codes.

Output:

--Create the Countries Table:
CREATE TABLE dbo.Countries (
    CountryCode CHAR(2) PRIMARY KEY, -- ISO 3166-1 alpha-2 code
    CountryName NVARCHAR(100) NOT NULL
);

--Modify the Addresses Table:
CREATE TABLE dbo.Addresses (
    AddressID INT IDENTITY(1,1) PRIMARY KEY,
    CustomerID INT,
    AddressLine1 NVARCHAR(200),
    AddressLine2 NVARCHAR(200),
    City NVARCHAR(100),
    State NVARCHAR(100),
    PostalCode NVARCHAR(20),
    CountryCode CHAR(2),
    FOREIGN KEY (CustomerID) REFERENCES dbo.Customers(CustomerID),
    FOREIGN KEY (CountryCode) REFERENCES dbo.Countries(CountryCode)
);

Prompt:
Extend the Countries table with ISO 3 characters code and ISO Number.

Output:

CREATE TABLE dbo.Countries (
    CountryCode CHAR(2) PRIMARY KEY, -- ISO 3166-1 alpha-2 code
    CountryName NVARCHAR(100) NOT NULL,
    ISO3Code CHAR(3), -- ISO 3166-1 alpha-3 code
    ISONumericCode INT -- ISO 3166-1 numeric code
);

Prompt:
Create a Regions table with Region code and description that allows to group the countries in territories. A country can be assigned to multiple regions.

Output:

-- Create the Regions Table:
CREATE TABLE dbo.Regions (
    RegionCode CHAR(3) PRIMARY KEY,
    RegionDescription NVARCHAR(100) NOT NULL
);

-- Create the Countries Table (including the previous columns):
CREATE TABLE dbo.Countries (
    CountryCode CHAR(2) PRIMARY KEY, -- ISO 3166-1 alpha-2 code
    CountryName NVARCHAR(100) NOT NULL,
    ISO3Code CHAR(3), -- ISO 3166-1 alpha-3 code
    ISONumericCode INT -- ISO 3166-1 numeric code
);

-- Create the CountryRegions Junction Table:
CREATE TABLE dbo.CountryRegions (
    CountryCode CHAR(2),
    RegionCode CHAR(3),
    PRIMARY KEY (CountryCode, RegionCode),
    FOREIGN KEY (CountryCode) REFERENCES dbo.Countries(CountryCode),
    FOREIGN KEY (RegionCode) REFERENCES dbo.Regions(RegionCode)
);

Prompt:
Create a view based on Customers that includes all the above tables. 

Output:

CREATE VIEW CustomerDetails AS
SELECT 
    c.CustomerID,
    c.CustomerName,
    c.ContactName,
    a.AddressLine1,
    a.AddressLine2,
    a.City,
    a.State,
    a.PostalCode,
    co.CountryCode,
    co.CountryName,
    co.ISO3Code,
    co.ISONumericCode,
    r.RegionCode,
    r.RegionDescription
FROM 
    dbo.Customers c
LEFT JOIN 
    dbo.Addresses a ON c.CustomerID = a.CustomerID
LEFT JOIN 
    dbo.Countries co ON a.CountryCode = co.CountryCode
LEFT JOIN 
    dbo.CountryRegions cr ON co.CountryCode = cr.CountryCode
LEFT JOIN 
    dbo.Regions r ON cr.RegionCode = r.RegionCode;

Prompt:
In the above view use 3 letter encoding for table's name. 

Output:

CREATE VIEW CustomerDetails AS
SELECT 
    cus.CustomerID,
    cus.CustomerName,
    cus.ContactName,
    add.AddressLine1,
    add.AddressLine2,
    add.City,
    add.State,
    add.PostalCode,
    cou.CountryCode,
    cou.CountryName,
    cou.ISO3Code,
    cou.ISONumericCode,
    reg.RegionCode,
    reg.RegionDescription
FROM 
    dbo.Customers cus
LEFT JOIN 
    dbo.Addresses add ON cus.CustomerID = add.CustomerID
LEFT JOIN 
    dbo.Countries cou ON add.CountryCode = cou.CountryCode
LEFT JOIN 
    dbo.CountryRegions cr ON cou.CountryCode = cr.CountryCode
LEFT JOIN 
    dbo.Regions reg ON cr.RegionCode = reg.RegionCode;

Unfortunately, the definition uses the "add" reserved word for addresses as prefix. Therefore, the following prompt:

Prompt:
Modify the above view to use 3-letter aliases that aren't as reserved words. 

Output:

CREATE VIEW dbo.CustomerDetails AS
SELECT 
    cus.CustomerID, -- Customer ID
    cus.CustomerName, -- Customer name
    cus.ContactName, -- Contact name
    adr.AddressLine1, -- First line of the address
    adr.AddressLine2, -- Second line of the address
    adr.City, -- City of the address
    adr.State, -- State or region of the address
    adr.PostalCode, -- Postal or ZIP code of the address
    cou.CountryCode, -- Country code
    cou.CountryName, -- Country name
    cou.ISO3Code, -- ISO 3166-1 alpha-3 code
    cou.ISONumericCode, -- ISO 3166-1 numeric code
    reg.RegionCode, -- Region code
    reg.RegionDescription -- Region description
FROM 
    dbo.Customers cus
LEFT JOIN 
    dbo.Addresses adr ON cus.CustomerID = adr.CustomerID
LEFT JOIN 
    dbo.Countries cou ON adr.CountryCode = cou.CountryCode
LEFT JOIN 
    dbo.CountryRegions cr ON cou.CountryCode = cr.CountryCode
LEFT JOIN 
    dbo.Regions reg ON cr.RegionCode = reg.RegionCode;

Prompt:
Modify the schema of the above objects to include the schema name.

Prompt:
Change the definition of the above tables and test whether the tables already exists.

Prompt:
Annotate the definition of the above objects with a comment.

Prompt:
Generate the scripts for inserting 10 customers with different addresses in the above tables.

Prompt:
Aggregate the update and insert commands into the same stored procedure.

Output:

CREATE PROCEDURE dbo.UpsertCustomer
    @CustomerID INT = NULL, -- Optional parameter for insert
    @CustomerName NVARCHAR(100),
    @ContactName NVARCHAR(100),
    @Country NVARCHAR(50)
AS
BEGIN
    IF EXISTS (SELECT 1 FROM dbo.Customers WHERE CustomerID = @CustomerID)
    BEGIN
        -- Update existing customer
        UPDATE dbo.Customers
        SET 
            CustomerName = @CustomerName,
            ContactName = @ContactName,
            Country = @Country
        WHERE 
            CustomerID = @CustomerID;
    END
    ELSE
    BEGIN
        -- Insert new customer
        INSERT INTO dbo.Customers (CustomerName, ContactName, Country)
        VALUES (@CustomerName, @ContactName, @Country);
    END
END;

I used to create similar code in the past to consolidate the logic for inserts and updates within the same stored procedure. This allows to reduce the number of objects created in a database and minimize the impact of some other issues (e.g. code duplication for validation).

I also use the comma before each line of text where it applies (and not at the end). However, this can be addressed with the following prompt:

Prompt: 
Rewrite the above database objects by butting the comma in front of each line where it applies.

Prompt:
Modify the view so each join condition is written on its own line.

Prompt:
Modify the view with create or alter command.

Prompt:
Modify the view to check for null values.

Comments:
1) I could use the scripts also in a SQL database, though one must pay attention to the order in which the steps must be run.
2) All these are basic steps; so, a natural question: how far can we go? One can generate further objects, and knowledge about the data model seems to be available in Copilot, though normal data models are much more complex than this! Probably, there's a limit beyond which Copilot will start to be inefficient as long the data model is not available explicitly. SQL Server Copilot would help probably to overcome such limitations, though the feature is still in limited preview.
3) I wonder whether given a set of best practices, Copilot will be able to use them and create the CRUD objects accordingly. More experiments are needed though.
4) It will be interesting to observe how much the prompts generated by other people lead to similar or different outcomes. (I observed that nontechnical prompts generated in the past different outcomes.)
5) The fact that I was able to change some formatting (e.g. comma before each line) for all objects with just a prompt frankly made my day! I can't stress enough how many hours of work the unformatted code required in the past! It will be interesting to check if this can be applied also to a complex database schema.

Happy coding!

Previous Post <<||>> Next Post

References:
[1] Microsoft 365 (2024) Overview of prompts [link]

Resources:
[R1] Microsoft 365 (2025) Microsoft 365 Copilot release notes [link]
[R2] Microsoft 365 (2025) What’s new in Microsoft 365 Copilot | Jan 2025 [link]
[R3] Microsoft 365 (2025) Copilot is now included in Microsoft 365 Personal and Family [link]

Acronyms:
LLM - large language model
CRUD - Create, Read, Update, Delete