SQL Server Metadata for Stored Procedures, Functions and Views

Problem

Please present T-SQL examples that help to create, use, and track SQL modules in SQL Server. I am particularly interested in SQL modules for user-defined functions, stored procedures, and views. Also, illustrate how to track and examine SQL modules with metadata queries.

Solution

SQL modules in SQL Server are containers for user-defined T-SQL code. Here is a list of the SQL module types:

• Stored procedures
• Scalar functions (referred to as user-defined functions in this tip)
• Table-valued functions
• Inline table-valued functions
• Views
• Triggers
• Rules
• Natively compiled scalar user-defined functions

This tip focuses on an introduction to three types of SQL modules:

1. A user-defined function is created with a create function statement. You can remove a user-defined function statement with a drop function statement. This type of SQL module returns a scalar value, such as an integer, date, or string, as opposed to a table of values with one or more columns.
2. A stored procedure is a more flexible type of SQL module in that it can return non-scalar as well as scalar values. An example of a non-scalar value is a table of scalar values with student names, class names, and grades for different classes. Additionally, a stored procedure frequently focuses on more than how to compute values. For example, a stored procedure can create a table for storing values, insert rows of data from a file to the table as well as insert and delete rows from a previously populated table. You can, respectively, create and remove stored procedure SQL modules from a database with create procedure and drop procedure statements.
3. A view SQL module displays data from one or more other data sources, such as tables or other views. You can add and drop a view from a database. The data for a view are specified with select statements.
   • If you have a production table, you can display selected columns from the data source full table in a view.
   • Alternatively, a view can join data from multiple data sources in a single view.
   • Another feature of views is that they allow the filtering of rows from a larger data source.

You can track the existence of different kinds of SQL modules in a database with metadata queries. You can also use metadata queries to review the contents of SQL modules in a database, such as the T-SQL used to define a SQL module.

A User-defined SQL Module Example

The bankersround user-defined function is an example of a scalar function SQL module. Prior MSSQLTips.com articles examine different versions of this custom scalar function and compare it to other approaches for rounding decimal values (here, here, and here). The example within this section highlights how to create, use, and track a SQL module that contains the bankersround function.

Articles on the bankersround function are popular because the function is easy to code and understand. Additionally, rounded values from the bankersround function more closely match their corresponding unrounded values than those from the SQL Server built-in round function. The following script illustrates how to create a fresh version of the bankersround function in the DataScience database. You can place and reference the function in any other database you prefer. There are two parts to the following script excerpt:

• Part 1 begins with commented code on how to create a version of the DataScience database. The first part ends with a use statement that references the DataScience database as the default database for the script.

• Part 2 of the script excerpt illustrates the application of a conditional drop function statement and a create function statement to create a fresh copy of the bankersround function. The create function statement must be the first T-SQL statement in a script block.
  • The go statement just before the create function statement marks the beginning of a script block for the statement.
  • The go statement after the last end statement causes the create function statement to execute.

/*
-- initiate creation of DataScience db
create database DataScience
go
 
*/
 
 
-- bankersround_udf
 
-- designate default database
use DataScience
 
-- create a fresh version of bankersround udf
drop function if exists dbo.bankersround;
 
go
create function dbo.bankersround (@number decimal(19, 4)) 
returns int
as
begin
  declare @integerpart int, @fractionalpart decimal(19, 4), @bankersround int
 
  set @integerpart = convert(int, @number)
  set @fractionalpart = @number - @integerpart
 
select @bankersround = case
   when @integerpart % 2 = 0 and @fractionalpart = .5000 then @integerpart
   when @fractionalpart < .5000 then @integerpart
   else @integerpart + 1
end
 
return @bankersround
end
go

The next script excerpt shows how to invoke both the bankersround function and the built-in SQL Server round function for three decimal values (.49, .50, .51).

-- test runs of the bankersround function with different input parameters
select 0.49 number, (dbo.bankersround(0.49)) [number rounded to integer]
select 0.50 number, (dbo.bankersround(0.50))  [number rounded to integer]
select 0.51 number, (dbo.bankersround(0.51)) [number rounded to integer]
 
-- test runs of the SQL Server round function with different input parameters
declare @decimal_value decimal(19,2)
 
set @decimal_value = 0.49
select @decimal_value [@decimal_value], round(@decimal_value,0) [round for @decimal_value]
 
set @decimal_value = 0.50
select @decimal_value [@decimal_value], round(@decimal_value,0) [round for @decimal_value]
 
set @decimal_value = 0.51
select @decimal_value [@decimal_value], round(@decimal_value,0) [round for @decimal_value]

Here is the Results tab from running the preceding script block in SQL Server Management Studio (SSMS).

• The top three values show rounded results from the bankersround function.
• The bottom three values show rounded results from the built-in SQL Server round function.
• The rounded values are different when the value to be rounded is .50. The best explanation for the cause of this difference is available here.

You can track SQL modules by returning column values from the sys.sql_modules catalog view. This catalog view has a row for each SQL module in a database. Some column values on a row of the sys.sql_modules catalog view can provide information about the object_id, which, in turn, can lead to additional information about a SQL module, such as the schema in which the module resides and the type of module. SQL modules reside in a database. By using the db_id and db_name functions, you can return information about the database in which a SQL module resides.

There are two different techniques for returning the underlying T-SQL code for a SQL module.

• This sp_helptext system stored procedure displays the T-SQL code from the create statement defining the SQL module. Each line of output from the system stored procedure corresponds to each line in the create statement for a SQL module.
• The object_definition function also returns the T-SQL code for the name of a SQL module. The return value from the function is a nvarchar(max) value.

The sp_helptext stored procedure often returns a definition that is easier to read by a developer. The object_definition function returns a value that is often more suitable for machine reading.

The final script segment in this section appears next. The code is for tracking and examining the SQL module contents created in this section.

• The use statement at the top of the segment specifies DataScience as the default database.
• The select statement extracts info about the database and the SQL module.
• The exec statement for the sp_helptext stored procedure returns lines of text for the code defining the bankersround SQL module.

-- designate a database for which to report info about sql modules
use DataScience
 
-- query sys.sql_modules, sys_objects, and sys.schemas
-- to return info about the bankersround SQL module
select 
        (select name from sys.databases where name = DB_NAME()) CurrentDatabaseName
      , (select database_id from sys.databases where database_id = DB_ID()) CurrentDatabaseID
        , sm.object_id
        , ss.[name] as [schema]
        , o.[name] as object_name
        , o.[type]
        , o.[type_desc]
FROM sys.sql_modules AS sm     
JOIN sys.objects AS o 
    ON sm.object_id = o.object_id  
JOIN sys.schemas AS ss
    ON o.schema_id = ss.schema_id  
ORDER BY 
      o.[type]
    , ss.[name]
    , o.[name];
   
-- text lines for stored procedure
exec sp_helptext 'bankersround';

Here are two results sets from the preceding script. The top panel shows the results set from the select statement. The bottom panel shows the output from the sp_helptext stored procedure for the SQL module named bankersround.

A Stored Procedure SQL Module Example

The second example for creating and tracking a SQL module demonstrates how to import two CSV files into SQL Server. The SQL module type is for a stored procedure instead of a scalar function, as in the preceding section.

• The first CSV file is selected in the first of the following pair of screenshots from a Notepad++ application. This file is for historical data from Yahoo Finance for the SPY ticker. The Notepad++ window title reveals the location of the CSV file.
• The second CSV file appears in the next screenshot. This file contains historical data from Yahoo Finance for the QQQ ticker.
• The files for the SPY and QQQ files contain historical data for August 2024.

The SQL module for this section imports the CSV file for each ticker into an empty staging table (dbo.PriceAndVolumeDataFromYahooFinance). Then, a fresh column is added to the results set for the ticker symbol in the results set. Because the example in this section imports just two tables into the final staging table (dbo.PriceAndVolumeDataFromYahooFinanceAcrossSymbols), the final staging table contains data from the SPY and QQQ tickers.

Here’s an excerpt for creating the SQL model (Populate_Tables_From_Files_ver_1) briefly summarized above.

/*
-- initiate creation of SecurityTradingAnalytics db
create database SecurityTradingAnalytics
go
 
*/
 
 
-- specify a default database
use SecurityTradingAnalytics
go
 
-- create fresh versions of tables to be used by the
-- dbo.Populate_Tables_From_Files_ver_1 stored proc
 
drop table if exists dbo.PriceAndVolumeDataFromYahooFinance
drop table if exists dbo.PriceAndVolumeDataFromYahooFinanceAcrossSymbols
 
create table dbo.PriceAndVolumeDataFromYahooFinance (
 date date not null
,[open] decimal(18, 6) not null
,high decimal(18, 6) not null
,low decimal(18, 6) not null
,[close] decimal(18, 6) not null
,adjclose decimal(18, 6) not null
,Volume bigint not null
)
 
create table dbo.PriceAndVolumeDataFromYahooFinanceAcrossSymbols(
   [Symbol] [nvarchar](8) NOT NULL,
   [date] [date] NOT NULL,
   [open] [decimal](18, 6) NOT NULL,
   [high] [decimal](18, 6) NOT NULL,
   [low] [decimal](18, 6) NOT NULL,
   [close] [decimal](18, 6) NOT NULL,
   [adjclose] [decimal](18, 6) NOT NULL,
   [Volume] [bigint] NOT NULL
) 
go
 
----------------------------------------------------------------------------
 
-- create a fresh version of dbo.Populate_Tables_From_Files_ver_1
-- stored proc
drop procedure if exists dbo.Populate_Tables_From_Files_ver_1
go
 
create procedure dbo.Populate_Tables_From_Files_ver_1
as
 
begin
 
-- bulk insert raw download from C:\Users\User\Downloads\SPY_Aug_2024.csv
-- into dbo.PriceAndVolumeFromYahooFinance
bulk insert dbo.PriceAndVolumeDataFromYahooFinance
from 'C:\Users\User\Downloads\SPY_Aug_2024.csv'
with
(
    firstrow = 2,
    fieldterminator = ',',  --CSV field delimiter
    rowterminator = '0x0a'  -- for lines ending with a lf and no cr
)
 
--insert into dbo.PriceAndVolumeDataFromYahooFinanceAcrossSymbols
insert into dbo.PriceAndVolumeDataFromYahooFinanceAcrossSymbols
select 'SPY' [Symbol], * 
from dbo.PriceAndVolumeDataFromYahooFinance
 
----------------------------------------------------------------------------
----------------------------------------------------------------------------
 
-- create a fresh version of dbo.PriceAndVolumeDataFromYahooFinance
-- populated with ...Downloads\QQQ_Aug_2024.csv
 
-- bulk insert raw download for QQQ into dbo.PriceAndVolumeFromYahooFinance
truncate table dbo.PriceAndVolumeDataFromYahooFinance
bulk insert dbo.PriceAndVolumeDataFromYahooFinance
from 'C:\Users\User\Downloads\QQQ_Aug_2024.csv'
with
(
    firstrow = 2,
    fieldterminator = ',',  --CSV field delimiter
    rowterminator = '0x0a'  -- for lines ending with a lf and no cr
)
 
-- insert QQQ symbol values into dbo.PriceAndVolumeDataFromYahooFinanceAcrossSymbols
insert into dbo.PriceAndVolumeDataFromYahooFinanceAcrossSymbols
select 'QQQ' [Symbol], * 
from dbo.PriceAndVolumeDataFromYahooFinance
 
end
go

The next script segment illustrates how to invoke an exec statement for the stored procedure created in the preceding script segment. The select statement in the following script segment displays the contents of the dbo.PriceAndVolumeDataFromYahooFinanceAcrossSymbols table.

-- execute dbo.Populate_Tables_From_Files_ver_1
exec dbo.Populate_Tables_From_Files_ver_1
 
 
-- display dbo.PriceAndVolumeDataFromYahooFinanceAcross_Symbols
select* from dbo.PriceAndVolumeDataFromYahooFinanceAcrossSymbols

The next two screenshots show excerpts with the first and last five rows from the select statement’s results set in the preceding script. You can verify the successful operation of the stored procedure by comparing the excerpted results for the SPY and QQQ tickers from the select statement to the CSV file listing presented earlier in this section.

After a stored procedure is created, SQL Server updates its sys.sql_modules catalog view to reflect the corresponding stored procedure. The T-SQL code for tracking the contents of the SQL module for a stored procedure can be identical to the code for tracking a user-defined function. Recall that each database has its own sys.sql_modules catalog view. Therefore, the basic code for tracking the Populate_Tables_From_Files_ver_1 SQL module is the same as for tracking the bankersround SQL module, except for the designation of a source database. The source database for the bankersround SQL module in the preceding section is the same as for the Populate_Tables_From_Files_ver_1 in this section.

For easy reference, here is the code for tracking the Populate_Tables_From_Files_ver_1 SQL module. Notice that the use statement at the beginning of the following script segment references the SecurityTradingAnalytics database. In contrast, the corresponding script for the preceding section designates DataScience as the name of its default database.

-- use SecurityTradingAnalytics
use SecurityTradingAnalytics
 
SELECT 
          sm.object_id
        , ss.[name] as [schema]
        , o.[name] as object_name
        , o.[type]
        , o.[type_desc]
FROM sys.sql_modules AS sm     
JOIN sys.objects AS o 
    ON sm.object_id = o.object_id  
JOIN sys.schemas AS ss
    ON o.schema_id = ss.schema_id  
ORDER BY 
      o.[type]
    , ss.[name]
    , o.[name];

Here is the results set from the preceding script excerpt. Notice that the type_desc property is SQL_STORED_PROCEDURE, and the object_name is Populate_Tables_From_Files_ver_1. The object_id for the SQL module in the following results set is 1077578877. In contrast, the corresponding results set from the preceding section has object_id, object_name, type_desc property values, respectively, of 1109578991, bankersround, and SQL_SCALAR_FUNCTION.

The T-SQL code for displaying the source code for the SQL module in this section appears in the following script.

-- text lines for stored procedure
exec sp_helptext 'Populate_Tables_From_Files_ver_1';

The following table contains one column with two rows. The table displays the results set from the preceding script excerpt. The line numbers denote the line numbers for the source text of the stored procedure.

A View SQL Module Example

A SQL Server user-defined view is a virtual table. A SQL Server developer can create a view by embedding a select statement inside a create view statement. You can specify a view as the data source for another select statement and extract data from the view as well as perform calculations based on the data returned from a view. Like user-defined functions and stored procedures, SQL Server tracks user-defined views in its sys.sql_modules catalog view. Each database tracks its own collection of SQL modules in the sys.sql_modules catalog view for a database. The type_desc column of a view SQL module is VIEW.

To create a view, you need a previously existing data source. This tip’s preceding section creates and populates a table named PriceAndVolumeDataFromYahooFinanceAcrossSymbols. Recall that the data stores downloaded data from Yahoo Finance for the SPY and QQQ tickers. The data for both tickers are for the trading days in August 2024. The TSQL code in this section creates and populates a second table (local_variable_values) with data from the first and last trading day for each ticker in August 2024.

The populated version of the local_variable_values table is referenced within a select statement for the local_variable_values_with_percentage_change_values view. The view uses the start and end dates for each ticker to compute and display the percentage change for each ticker during August 2024.

The following script demonstrates an approach for processing a data source, such as the PriceAndVolumeDataFromYahooFinanceAcrossSymbols table, to create an underlying data source for the local_variable_values_with_percentage_change_values view. The percentage change for each ticker is computed based on the difference in the end closing value versus the start closing value for the dates during August 2024. The last select statement in the following script displays the results computed from within the view.

use SecurityTradingAnalytics
go
 
-- create a fresh version of table for local variable values
drop table if exists local_variable_values;
create table local_variable_values
(
 target_ticker nvarchar(8)
,start_date date
,end_date date
,start_close decimal(18,6)
,end_close decimal(18,6)
);
 
 
-- local variable declarations and assignments for SPY ticker
declare 
 @target_ticker nvarchar(8) = 'SPY'
,@start_date date
,@end_date date
,@start_close decimal(18,6)
,@end_close decimal(18,6)
 
-- computed local variable values for SPY ticker
set @start_date = 
 (select min(date) from [dbo].[PriceAndVolumeDataFromYahooFinanceAcrossSymbols] 
         where Symbol = @target_ticker)
set @end_date =
 (select max(date) from [dbo].[PriceAndVolumeDataFromYahooFinanceAcrossSymbols] 
      where Symbol = @target_ticker) 
set @start_close =
 (select [close] from [dbo].[PriceAndVolumeDataFromYahooFinanceAcrossSymbols] 
         where Symbol = @target_ticker and date = @start_date ) 
set @end_close = 
 (select [close] from [dbo].[PriceAndVolumeDataFromYahooFinanceAcrossSymbols] 
         where Symbol = @target_ticker and date = @end_date )
 
-- local variable values for SPY ticker inputs
insert into local_variable_values 
(target_ticker, start_date, end_date, start_close, end_close)
select 
 @target_ticker [@target_ticker]
,@start_date [@start_date]
,@end_date [@end_date]
,@start_close [@start_close]
,@end_close [@end_close]
 
 
-- local variable declarations and assignments for QQQ ticker
 
set @target_ticker = 'QQQ'
set @start_date = 
 (select min(date) from [dbo].[PriceAndVolumeDataFromYahooFinanceAcrossSymbols] 
         where Symbol = @target_ticker)
set @end_date =
 (select max(date) from [dbo].[PriceAndVolumeDataFromYahooFinanceAcrossSymbols] 
      where Symbol = @target_ticker) 
set @start_close =
 (select [close] from [dbo].[PriceAndVolumeDataFromYahooFinanceAcrossSymbols] 
         where Symbol = @target_ticker and date = @start_date ) 
set @end_close = 
 (select [close] from [dbo].[PriceAndVolumeDataFromYahooFinanceAcrossSymbols] 
         where Symbol = @target_ticker and date = @end_date )
 
-- local variable values for QQQ ticker inputs
insert into local_variable_values 
(target_ticker, start_date, end_date, start_close, end_close)
select 
 @target_ticker [@target_ticker]
,@start_date [@start_date]
,@end_date [@end_date]
,@start_close [@start_close]
,@end_close [@end_close]
 
 
drop view if exists dbo.local_variable_values_with_percentage_change_values
-- add percentage change column to the select statement
-- on which the view is based
go
create view dbo.local_variable_values_with_percentage_change_values
as
select 
 *,format((end_close/start_close) - 1, 'p2') [percentage change]
from local_variable_values
go
 
-- display view contents
select * from dbo.local_variable_values_with_percentage_change_values

Here is the results set from the select statement at the end of the preceding script. As you can see, for the particular date range over which percentage change is computed, the SPY and QQQ start_close and end_close values have nearly identical percentage change values (3.81% versus 3.61%).

The following script tracks the metadata elements created by the preceding TSQL script.

• The use statement at the top of the script designates the SecurityTradingAnalytics database as the default database for the script. This is because the local_variable_values_with_percentage_change_values view resides in this database.
• After the use statement, the script returns information for the SQL modules in the default database. There are two SQL modules in the database – one (Populate_Tables_From_Files_ver_1) is from the preceding section and the other (local_variable_values_with_percentage_change_values) is from the current section.
• Next, a sp_helptext statement displays the text for creating the local_variable_values_with_percentage_change_values view.
• The final segment of the following script displays the column name, data type name, maximum column size in bytes, and whether a column is nullable within the source data for the local_variable_values_with_percentage_change_values view.

-- designate a database for which to report info about sql modules
use SecurityTradingAnalytics
 
-- query for selected properties of sys.sql_modules in current database
-- properties are object_id, schema, object_name, type, and type_desc
select 
        (select name from sys.databases where name = DB_NAME()) CurrentDatabaseName
      , (select database_id from sys.databases where database_id = DB_ID()) CurrentDatabaseID
        , sm.object_id
        , ss.[name] as [schema]
        , o.[name] as object_name
        , o.[type]
        , o.[type_desc]
FROM sys.sql_modules AS sm     
JOIN sys.objects AS o 
    ON sm.object_id = o.object_id  
JOIN sys.schemas AS ss
    ON o.schema_id = ss.schema_id  
ORDER BY 
      o.[type]
    , ss.[name]
    , o.[name];
   
-- text lines for view
exec sp_helptext 'local_variable_values_with_percentage_change_values';
 
 
-- name, data type, max size, is nullable properties of columns in source
-- data for local_variable_values_with_percentage_change_values view
SELECT
    c.name AS ColumnName,
    t.name AS DataType,
    c.max_length AS MaxLength,
    c.is_nullable AS IsNullable
FROM sys.tables AS tbl
INNER JOIN sys.columns AS c ON tbl.object_id = c.object_id
INNER JOIN sys.types AS t ON c.system_type_id = t.system_type_id
WHERE tbl.name = 'local_variable_values';

The following table displays in a separate row each of the three results sets from the preceding script. Notice particularly that the first results set has one line of information for the Populate_Tables_From_Files_ver_1 SQL module created in the preceding section and a second line of information generated by the code for the local_variable_values_with_percentage_change_values SQL module in this section.

Next Steps

In my experience, the kinds of metadata described in this tip are most useful when working with legacy databases that I did not personally create. SQL module objects can also be helpful when there is a need to automate the documentation of T-SQL code containers. The examples in this tip are meant to introduce you to the power of tracking and documenting T-SQL code containers, such as user-defined functions, stored procedures, and user-defined views.