💎💫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!

📊Graphical Representation: Graphics We Live By (Part XI: Comparisons Between Data Series)

Graphical Representation Series

Over the past 10-20 years it became so easy to create data visualizations just by dropping some of the data available into a tool like Excel and providing a visual depiction of it with just a few clicks. In many cases, the first draft, typically provided by default in the tool used, doesn't even need further work as the objective was reached, while in others the creator must have a minimum skillset for making the visualization useful, appealing, or whatever quality is a final requirement for the work in scope. However, the audience might judge the visualization(s) from different perspectives, and there can be a broad audience with different skills in reading, evaluating and understanding the work.

There are many depictions on the web resembling the one below, taken from a LinkedIn post:

Example Chart - Boing vs. Airbus

Even if the visualization is not perfect, it does a fair job in representing the data. Improvements can be made in the areas of labels, the title and positioning of elements, and the color palette used. At least these were the improvements made in the original post. It must be differentiated also between the environment in which the charts are made available, the print format having different characteristics than the ones in business setups. Unfortunately, the requirements of the two are widely confused, probably also because of the overlapping of the mediums used. 

Probably, it's a good idea to always start with the row data (or summaries of it) when the result consists of only a few data points that can be easily displayed in a table like the one below (the feature to round the decimals for integer values should be available soon in Power BI):

Summary Table

Of course, one can calculate more meaningful values like percentages from the total, standard deviations and other values that offer more perspectives into the data. Even if the values adequately reflect the reality, the reader can but wonder about the local and global minimal/maximal values, without talking much about the meaning of data points, which is easily identifiable in a chart. At least in the case of small data sets, using a table in combination with a chart can provide a more complete perspective and different ways of analyzing the data, especially when the navigation is interactive. 

Column and bar charts do a fair job in comparing values over time, though they do use a lot of ink in the process (see D). While they make it easy to compare neighboring values, the rectangles used tend to occupy a lot of space when they are made too wide or too high to cover the empty space within the display (e.g. when just a few values are displayed, space being wasted in the process). As the main downside, it takes a lot of scanning until the reader identifies the overall trends, and the further away the bars are from each other, the more difficult it becomes to do comparisons. 

In theory, line charts are more efficient in representing the above data points, because the marks are usually small and the line thin enough to provide a better data-ink ratio, while one can see a lot at a glance. In Power BI the creator can use different types of interpolation: linear (A), step (B) or smooth (C). In many cases, it might be a good idea to use a linear interpolation, though when there are no or minimal overlapping, it might be worthwhile to explore the other types if interpolation too (and further request feedback from the users):

Linear, Step and Smooth Line Charts

The nearness of values from different series can raise difficulties in identifying adequately the points, respectively delimiting the lines (see B).When the density of values allows it, it makes sense also to include the averages for each data series to reflect the distance between the two data sets. Unfortunately, the chart can get crowded if further data series or summaries are added to the cart(s). 

If the column chart (E) is close to the redesigned chart provided in the original redesign, the other alternatives can provide upon case more value. Stacked column charts (D) allow also to compare the overall quantity by month, area charts (F) tend to use even more color than needed, while water charts (G) allow to compare the difference between data points per time unit. Tornado charts (H) are a variation of bar charts, allowing easier comparing of the size of the bars, while ribbon charts (I) show well the stacking values. 

Alternatives to Line Charts

One should consider changing the subtitle(s) slightly to reflect the chart type when the patterns shown imply a shift in attention or meaning. Upon case, more that one of the above charts can be used within the same report when two or more perspectives are important. Using a complementary perspective can facilitate data's understanding or of identifying certain patterns that aren't easily identifiable otherwise. 

In general, the graphics creators try to use various representational means of facilitating a data set's understanding, though seldom only two series or a small subset of dimensions provide a complete description. The value of data comes when multiple perspectives are combined. Frankly, the same can be said about the above data series. Yes, there are important differences between the two series, though how do the numbers compare when one looks at the bigger picture, especially when broken down on element types (e.g. airplane size). How about plan vs. actual values, how long does it take more for production or other processes? It's one of a visualization's goals to improve the questions posed, but how efficient are visualizations that barely scratch the surface?

In what concerns the code, the following scripts can be used to prepare the data:

-- Power Query script (Boeing vs Airbus)
= let
    Source = let
    Source = #table({"Sorting", "Month Name", "Serial Date", "Boeing Deliveries", "Airbus Deliveries"},
    {
        {1, "Oct", #date(2023, 10, 31), 30, 50},
        {2, "Nov", #date(2023, 11, 30), 40, 40},
        {3, "Dec", #date(2023, 12, 31), 40, 110},
        {4, "Jan", #date(2024, 1, 31), 20, 30},
        {5, "Feb", #date(2024, 2, 29), 30, 40},  // Leap year adjustment
        {6, "Mar", #date(2024, 3, 31), 30, 60},
        {7, "Apr", #date(2024, 4, 30), 40, 60},
        {8, "May", #date(2024, 5, 31), 40, 50},
        {9, "Jun", #date(2024, 6, 30), 50, 80},
        {10, "Jul", #date(2024, 7, 31), 40, 90},
        {11, "Aug", #date(2024, 8, 31), 40, 50},
        {12, "Sep", #date(2024, 9, 30), 30, 50}
    }
    ),
    #"Changed Types" = Table.TransformColumnTypes(Source, {{"Sorting", Int64.Type}, {"Serial Date", type date}, {"Boeing Deliveries", Int64.Type}, {"Airbus Deliveries", Int64.Type}})
in
    #"Changed Types"
in
    Source

It can be useful to create the labels for the charts dynamically:

-- DAX code for labels
MaxDate = Format(Max('Boeing vs Airbus'[Serial Date]),"MMM-YYYY")
MinDate = FORMAT (Min('Boeing vs Airbus'[Serial Date]),"MMM-YYYY")
MinMaxDate = [MinDate] & " to " & [MaxDate]
Title Boing Airbus = "Boing and Airbus Deliveries " & [MinMaxDate]

Happy coding!

Previous Post <<||>> Next Post

🏭💠🗒️Microsoft Fabric: T-SQL Notebook [Notes] 🆕

Disclaimer: This is work in progress intended to consolidate information from various sources for learning purposes. For the latest information please consult the documentation (see the links below)! 

Last updated: 25-Feb-2024

[Microsoft Fabric] T-SQL notebook

• {def} notebook that enables to write and run T-SQL code within a notebook [1]
• {feature} allows to manage complex queries and write better markdown documentation [1]
• {feature} allows the direct execution of T-SQL on
• connected warehouse
• SQL analytics endpoint
• ⇐ queries can be run directly on the connected endpoint [1]
• multiple connections are allowed [1]
• allows running cross-database queries to gather data from multiple warehouses and SQL analytics endpoints [1]
• the code is run by the primary warehouse
• used as default in commands which supports three-part naming, though no warehouse was provided [1]
• three-part naming consists of 
• database name
• the name of the warehouse or SQL analytics endpoint [1]
• schema name
• table name
• {feature} autogenerate T-SQL code using the code template from the object explorer's context [1] menu
• {concept} code cells
• allow to create and run T-SQL code
• each code cell is executed in a separate session [1]
• {limitation} the variables defined in one cell are not available in another cell [1]
• one can check the execution summary after the code is executed [1]
• cells can be run individually or together [1]
• one cell can contain multiple lines of code [1]
• users can select and run subparts of a cell’s code [1]
• {feature} Table tab
• lists the records from the returned result set
• if the execution contains multiple result set, you can switch from one to another via the dropdown menu [1]
• a query can be saved as 
• view
• via 'Save as' view
• {limitation} does not support three-part naming [1]
• the view is always created in the primary warehouse [1]
• by setting the warehouse as the primary warehouse [1]
• table
• via 'Save as' table
• saved as CTAS 
• ⇐ 'Save as' is only available for the selected query text
• the query text must be selected before using the Save as options
• {limitation} doesn’t support 
• parameter cell
• the parameter passed from pipeline or scheduler can't be used [1]
• {feature} Recent Run 
• {workaround} use the current data warehouse monitoring feature to check the execution history of the T-SQL notebook [1]
• {feature} the monitor URL inside the pipeline execution
• {feature} snapshot 
• {feature} Git support 
• {feature} deployment pipeline support 

Previous Post  <<||>> Next Post 

References:

[1] Microsoft Learn (2025) T-SQL support in Microsoft Fabric notebooks [link] 

[2] Microsoft Learn (2025) Create and run a SQL Server notebook [link] 

[3] Microsoft Learn (2025) T-SQL surface area in Microsoft Fabric [link] 

[4] Microsoft Fabric Updates Blog (2024) Announcing Public Preview of T-SQL Notebook in Fabric [link]

Resources:

[R1] Microsoft Learn (2025) Fabric: What's new in Microsoft Fabric? [link]

Acronyms
CTAS - Create Table as Select
T-SQL - Transact SQL

💎🏭SQL Reloaded: Microsoft Fabric's SQL Databases (Part X: Templates for Database Objects)

One of the new features remarked in SQL databases when working on the previous post is the availability of templates in SQL databases. The functionality is useful even if is kept to a minimum. Probably, more value can be obtained when used in combination with Copilot, which requires at least a F12 capacity.

Schemas

Schemas are used to create a logical grouping of objects such as tables, stored procedures, and functions. From a structural and security point of view it makes sense to create additional schemas to manage the various database objects and use the default dbo schema only occasionally (e.g. for global created objects).

-- generated template - schema
CREATE SCHEMA SchemaName

-- create schema
CREATE SCHEMA Test

One can look at the sys.schemas to retrieve all the schemas available:

-- retrieve all schemas
SELECT schema_id
, name
, principal_id
FROM sys.schemas
ORDER BY schema_id

Tables

Tables, as database objects that contain all the data in a database are probably the elements that need the greatest attention in design and data processing. In some cases a table can be dedenormalized and it can store all the data needed, much like in MS Excel, respectively, benormalized in fact and dimension tables. 

Tables can be created explicitly by defining in advance their structure (see Option 1), respectively on the fly (see Option 2). 

-- Option 1

-- create the table manually (alternative to precedent step
CREATE TABLE [Test].[Customers](
	[CustomerId] [int] NOT NULL,
	[AddressID] [int] NULL,
	[Title] [nvarchar](8) NULL,
	[FirstName] [nvarchar](50) NULL,
	[LastName] [nvarchar](50) NULL,
	[CompanyName] [nvarchar](128) NULL,
	[SalesPerson] [nvarchar](256) NULL
) ON [PRIMARY]
GO

-- insert records
INSERT INTO Test.Customers
SELECT CustomerId
, Title
, FirstName 
, LastName
, CompanyName
, SalesPerson
FROM SalesLT.Customer


-- checking the output (both scenarios)
SELECT top 100 *
FROM Test.Customers

One can look at the sys.tables to retrieve all the tables available:

-- retrieve all tables
SELECT schema_name(schema_id) schema_name
, object_id
, name
FROM sys.tables
ORDER BY schema_name
, name

Views

Views are much like virtual table based on the result-set of an SQL statement that combines data from one or multiple tables.  They can be used to encapsulate logic, respectively project horizontally or  vertically a subset of the data. 

-- create view
CREATE OR ALTER VIEW Test.vCustomers
-- Customers 
AS
SELECT CST.CustomerId 
, CST.Title
, CST.FirstName 
, IsNull(CST.MiddleName, '') MiddleName
, CST.LastName 
, CST.CompanyName 
, CST.SalesPerson 
FROM SalesLT.Customer CST

-- test the view 
SELECT *
FROM Test.vCustomers
WHERE CompanyName = 'A Bike Store'

One can look at the sys.views to retrieve all the views available:

-- retrieve all views
SELECT schema_name(schema_id) schema_name
, object_id
, name
FROM sys.views
ORDER BY schema_name
, name

User-Defined Functions

A user-defined function (UDF) allows to create a function by using a SQL expression. It can be used alone or as part of a query, as in the below example.

-- generated template - user defined function
CREATE FUNCTION [dbo].[FunctionName] (
    @param1 INT,
    @param2 INT
)
RETURNS INT AS BEGIN RETURN
    @param1 + @param2
END

-- user-defined function: 
CREATE OR ALTER FUNCTION Test.GetFirstMiddleLastName (
    @FirstName nvarchar(50),
    @MiddleName nvarchar(50),
    @LastName nvarchar(50)
)
RETURNS nvarchar(150) AS 
BEGIN 
   RETURN IsNull(@FirstName, '') + IsNull(' ' + @MiddleName, '') + IsNull(' ' + @LastName, '') 
END

-- test UDF on single values
SELECT Test.GetFirstMiddleLastName ('Jack', NULL, 'Sparrow')
SELECT Test.GetFirstMiddleLastName ('Jack', 'L.', 'Sparrow')

-- test UDF on a whole table
SELECT TOP 100 Test.GetFirstMiddleLastName (FirstName, MiddleName, LastName)
FROM SalesLT.Customer

One can look at the sys.objects to retrieve all the scalar functions available:

-- retrieve all scalar functions
SELECT schema_name(schema_id) schema_name
, name
, object_id
FROM sys.objects 
WHERE type_desc = 'SQL_SCALAR_FUNCTION'
ORDER BY schema_name
, name

However, UDFs prove to be useful when they mix the capabilities of functions with the ones of views allowing to create a "parametrized view" (see next example) or even encapsulate a multi-line statement that returns a dataset. Currently, there seems to be no template available for creating such functions.

-- table-valued function
CREATE OR ALTER FUNCTION Test.tvfGetCustomers (
    @CompanyName nvarchar(50) NULL
)
RETURNS TABLE
-- Customers by Company
AS
RETURN (
	SELECT CST.CustomerId 
	, CST.CompanyName
	, CST.Title
	, IsNull(CST.FirstName, '') + IsNull(' ' + CST.MiddleName, '') + IsNull(' ' + CST.LastName, '') FullName
	, CST.FirstName 
	, CST.MiddleName 
	, CST.LastName 
	FROM SalesLT.Customer CST
	WHERE CST.CompanyName = IsNull(@CompanyName, CST.CompanyName)
);

-- test function for values
SELECT *
FROM Test.tvfGetCustomers ('A Bike Store')
ORDER BY CompanyName
, FullName

-- test function for retrieving all values
SELECT *
FROM Test.tvfGetCustomers (NULL)
ORDER BY CompanyName
, FullName

One can look at the sys.objects to retrieve all the table-valued functions available:

-- retrieve all table-valued functions
SELECT schema_name(schema_id) schema_name
, name
, object_id
FROM sys.objects 
WHERE type_desc = 'SQL_INLINE_TABLE_VALUED_FUNCTION'
ORDER BY schema_name
, name

Stored Procedures

A stored procedure is a prepared SQL statement that is stored as a database object and precompiled. Typically, the statements considered in SQL functions can be created also as stored procedure, however the latter doesn't allow to reuse the output directly.

-- get customers by company
CREATE OR ALTER PROCEDURE Test.spGetCustomersByCompany (
    @CompanyName nvarchar(50) NULL
)
AS
BEGIN
	SELECT CST.CustomerId 
	, CST.CompanyName
	, CST.Title
	, IsNull(CST.FirstName, '') + IsNull(' ' + CST.MiddleName, '') + IsNull(' ' + CST.LastName, '') FullName
	, CST.FirstName 
	, CST.MiddleName 
	, CST.LastName 
	FROM SalesLT.Customer CST
	WHERE CST.CompanyName = IsNull(@CompanyName, CST.CompanyName)
	ORDER BY CST.CompanyName
	, FullName
END 

-- test the procedure 
EXEC Test.spGetCustomersByCompany NULL -- all customers
EXEC Test.spGetCustomersByCompany 'A Bike Store' -- individual customer

One can look at the sys.objects to retrieve all the stored procedures available:

-- retrieve all scalar functions
SELECT schema_name(schema_id) schema_name
, name
, object_id
FROM sys.objects 
WHERE type_desc = 'SQL_STORED_PROCEDURE'
ORDER BY schema_name
, name

In the end, don't forget to drop the objects created above (note the order of the dependencies):

-- drop function 
DROP FUNCTION IF EXISTS Test.GetFirstMiddleLastName

-- drop function 
DROP FUNCTION IF EXISTS Test.tvfGetCustomers 
-- drop precedure
DROP VIEW IF EXISTS Test.Test.spGetCustomersByCompany

-- drop view 
DROP VIEW IF EXISTS Test.vCustomers

-- drop schema
DROP SCHEMA IF EXISTS Test

Previous Post <<||>> Next Post

References:
[1] Microsoft Learn (2024) Microsoft Fabric: Overview of Copilot in Fabric [link]