SQL Server includes a wide range of built-in functions that you can incorporate into your T-SQL expressions. One type of function that can be particularly useful, especially when working with system catalog views, is the metadata function. Metadata functions return information about the server instance, the databases on that instance, and the objects within the databases.
A metadata function is both scalar and nondeterministic. This means that the function returns only single data value and that value will not necessarily be the same each time you run the function, even if you pass in the same input values.
Many of the metadata functions focus on returning the names or IDs of objects such as tables, views, schemas, and data types, as well as databases, database files. and filegroups. Some metadata functions let you retrieve property values for database objects, the databases themselves, or the server instance. There are also functions for carrying out other tasks, such as finding a column’s length or returning an object’s definition in the form of a T-SQL CREATE
statement.
The more familiar you are with the metadata functions, the faster you can find the information you need when you need it. You can use them just like you can any scalar function, often using only a simple SELECT
clause to retrieve a specific bit of information.
In this article, I provide examples of T-SQL statements that use metadata functions in various ways. I created the examples on a local instance of SQL Server 2014, with most of the queries targeting the AdventureWorks2014
or AdventureWorksDW2014
databases. I’ve tried to cover as many of the functions as possible, but know that there are more, so be sure to refer to SQL Server’s documentation for additional information.
Retrieving object IDs
Each database is made up of objects such as tables, views, primary keys, check constraints, stored procedures, and sequence objects. SQL Server assigns each of these objects a unique ID. In some cases, you will need to know that ID to get specific information about the object. For example, some system views, adhering to good relational design principles, refer to an object only by its ID.
Unless you have memorized all the IDs for every object in your database, chances are you will need help in associating a name with an ID. One of the most useful metadata functions (and perhaps one of the most useful built-in functions) is OBJECT_ID
, which returns the database identifier for the specified object.
For example, on my instance of SQL Server, the following SELECT
statement returns 1237579447
as the identifier for the HumanResources.Employee
table in the AdventureWorks2014
database:
1 |
SELECT OBJECT_ID(N'AdventureWorks2014.HumanResources.Employee'); |
When calling the the OBJECT_ID
function, we must ensure that we target the correct object and the correct database. However, if the AdventureWorks2014
database is already the active database, we can drop its name from the argument we pass into the function:
1 2 3 4 |
USE AdventureWorks2014; GO SELECT OBJECT_ID(N'HumanResources.Employee'); |
You can use the OBJECT_ID
function to find the ID of any schema-scoped object, that is, an object that is associated with or belongs to a specific schema. For example, a user table is always defined as part of a schema, even if it defaults to dbo
, but a data definition language (DDL) trigger is not. You can find a list of SQL Server’s schema-scoped objects by referring to the TechNet topic sys.objects (Transact-SQL).
One of the handiest uses of the OBJECT_ID
function is to verify an object’s existence before taking a specific action, such as dropping a table. In the following example, I use an IF
statement to test whether the OBJECT_ID
function returns a value for the dbo.EmployeeInfo
table:
1 2 3 |
IF OBJECT_ID(N'dbo.EmployeeInfo', N'U') IS NOT NULL DROP TABLE dbo.EmployeeInfo; GO |
If the table exists, the function will return the identifier and the DROP
TABLE
statement will run. If the table does not exist, the function will return a NULL
value and the DROP
TABLE
statement will not run.
When working with system views, you will often need to use the OBJECT_ID
function because the view does not include the object’s name. For example, the sys.columns
catalog view returns information about all columns defined within a database. If you want to find the columns for a specific table, you need to include a WHERE
clause in your query that filters on the table’s object ID, as shown in the following example:
1 2 3 |
SELECT name, column_id FROM sys.columns WHERE object_id = OBJECT_ID(N'Person.ContactType'); |
As is often the case, we know an object’s name, but not its ID. However, rather than having to join the sys.columns
view to the sys.objects
view to get the object name, we can simply use the OBJECT_ID
function, giving us the following results:
name |
column_id |
ContactTypeID |
1 |
Name |
2 |
ModifiedDate |
3 |
Of course, not all objects are schema-scoped. There are databases and logical files and data types and the schemas themselves. For this reason, SQL Server includes a number of other metadata functions for retrieving an object’s ID. For example, suppose we want to retrieve data from sys.tables
for the Person
schema. We can use the SCHEMA_ID
function to pass in the schema name, rather than having to know its ID:
1 2 3 |
SELECT name, max_column_id_used FROM sys.tables WHERE schema_id = SCHEMA_ID(N'Person'); |
The SCHEMA_ID
function works just like the OBJECT_ID
function in terms of saving us from having to join the table or look up the ID. The SELECT
statement returns the following results:
name |
max_column_id_used |
Address |
9 |
AddressType |
4 |
StateProvince |
8 |
BusinessEntity |
3 |
BusinessEntityAddress |
5 |
BusinessEntityContact |
5 |
ContactType |
3 |
CountryRegion |
3 |
EmailAddress |
5 |
Password |
5 |
Person |
13 |
PersonPhone |
4 |
PhoneNumberType |
3 |
SQL Server also assigns IDs to system and user-defined data types, so it should come as no surprise that T-SQL provides the TYPE_ID
function. The WHERE
clause in the following SELECT
statement includes the OBJECT_ID
and TYPE_ID
functions in order to limit the results:
1 2 3 4 |
SELECT name, is_nullable FROM sys.columns WHERE object_id = OBJECT_ID(N'Person.Person') AND user_type_id = TYPE_ID(N'name'); |
The WHERE
clause uses the OBJECT_ID
function to return the ID of the Person.Person
table and uses the TYPE_ID
function to return the ID of the Name
user-defined data type, giving us the following results:
name |
is_nullable |
FirstName |
0 |
MiddleName |
1 |
LastName |
0 |
There is even a metadata function for retrieving the ID of a security principal in the current database, as shown in the following example:
1 2 3 |
SELECT name, schema_id FROM sys.schemas WHERE principal_id = DATABASE_PRINCIPAL_ID(N'dbo'); |
In this case, the DATABASE_PRINCIPAL_ID
function returns the ID of the dbo
principal, which is associated with the schemas shown in the following results:
name |
schema_id |
dbo |
1 |
HumanResources |
5 |
Person |
6 |
Production |
7 |
Purchasing |
8 |
Sales |
9 |
For some metadata functions, you do not always have to pass in a value when calling the function, in which case, SQL Server uses the context of the current database. For example, the DB_ID
function can return the ID of the current database without specifying the name of that database:
1 2 3 |
SELECT database_id, compatibility_level, collation_name FROM sys.databases WHERE database_id = DB_ID(); |
The statement uses the function to narrow down the data from the sys.databases
catalog view, without having to specify the database name, giving us the following results:
database_id |
compatibility_level |
collation_name |
9 |
120 |
SQL_Latin1_General_CP1_CI_AS |
Being able to use a metadata function in this way can make our code more portable and provide a mechanism for verifying our environment. For example, we can use the DATABASE_PRINCIPAL_ID
function to verify whether an operation should be carried out, based on the current user (principal):
1 2 3 4 |
IF DATABASE_PRINCIPAL_ID() = 1 PRINT 'The trigger will fire.'; ELSE PRINT 'The trigger will not file.'; |
In this case, I’m using PRINT
statements to demonstrate the logic, but you get the point. We can construct our T-SQL code in such a way that the trigger will fire only if a specific user connects to the database (in this instance, dbo
).
Before we move on to other types of metadata functions, there are a couple more of the ID type I wanted to point out, just to demonstrate the various identifiers you can retrieve. The following example uses the FILE_IDEX
function to return the ID for the specified logical file, in this case, AdventureWorks2014_Log
:
1 |
SELECT FILE_IDEX('AdventureWorks2014_Log'); |
Note that SQL Server also supports the FILE_ID
function, but that has been deprecated and will be removed in a future version of SQL Server.
If you instead want to retrieve a filegroup’s ID, you can use the FILEGROUP_ID
function:
1 |
SELECT FILEGROUP_ID('MemOptFilegroup') |
The examples shown in this section should give you a good sense of how you can use metadata functions to retrieve object IDs, but it’s also just as important to be able to retrieve object names, so let’s move on to the next set of examples.
Retrieving object names
At the top of the list of name-related functions is OBJECT_NAME
, which retrieves the name of a schema-scoped object, based on the specified object ID.
The following example uses the OBJECT_NAME
function in the SELECT
clause to return the names of specific tables and views:
1 2 3 4 5 |
SELECT OBJECT_NAME(object_id) TableName FROM sys.columns WHERE user_type_id = TYPE_ID(N'name') AND is_nullable = 1 GROUP BY object_id ORDER BY TableName; |
The SELECT
statement returns the tables and views that contain nullable columns configured with the Name
user-defined data type, giving us the following results:
TableName |
Person |
vAdditionalContactInfo |
vEmployee |
vEmployeeDepartment |
vEmployeeDepartmentHistory |
vIndividualCustomer |
vSalesPerson |
vStoreWithContacts |
vVendorWithContacts |
Functions such as OBJECT_NAME
work within the context of the current database. For this reason, when using this type of function to retrieve information from a database other than the current one, you must specify that database when calling the function, even if you include the target database in the FROM
clause.
Consider the following SELECT
statement, which retrieves data from the sys.columns
view in the AdventureWorksDW2014
database, but from within the context of the AdventureWorks2014
database:
1 2 3 4 5 6 7 8 |
USE AdventureWorks2014; GO SELECT OBJECT_NAME(object_id) TableCurrentDB, OBJECT_NAME(object_id, DB_ID(N'AdventureWorksDW2014')) TableRefDB, name ColumnName FROM AdventureWorksDW2014.sys.columns WHERE object_id = 677577452; |
The first two expressions in the SELECT
clause use the OBJECT_NAME
function to retrieve the object name based on the value in the object_id
column of the target table. However, the second instance of the function also passes in the database ID, giving us the following results:
TableCurrentDB |
TableRefDB |
ColumnName |
BusinessEntityAddress |
FactInternetSalesReason |
SalesOrderNumber |
BusinessEntityAddress |
FactInternetSalesReason |
SalesOrderLineNumber |
BusinessEntityAddress |
FactInternetSalesReason |
SalesReasonKey |
Because we do not specify the database in the first function instance, the returned information is actually coming from the AdventureWorks2014
database, as evidenced by the different results in the first two columns. So use caution when retrieving data from a database other than the current one. You could end up with inaccurate information without suspecting anything is wrong.
SQL Server also provides the SCHEMA_NAME
function for returning the schema name for a specified ID. If no ID is specified, the function returns the name of the default schema associated with the connected user. The following SELECT
statement uses the function in the WHERE
clause, taking the ID from the schema_id
column:
1 2 3 |
SELECT name PrimaryKey, object_id ObjectID FROM sys.objects WHERE SCHEMA_NAME(schema_id) = 'dbo' AND type = 'PK'; |
The statement uses the SCHEMA_NAME
function to return information about the primary keys within the dbo
schema, giving us the following results:
PrimaryKey |
ObjectID |
pk33 |
52195236 |
AdvisorTest3_primaryKey |
84195350 |
PK_ErrorLog_ErrorLogID |
309576141 |
PK__LookupTe__6D8B9C6BA3B225AC |
532196946 |
PK__AdvisorT__8CC3310032114505 |
564197060 |
PK_AWBuildVersion_SystemInformationID |
1070626857 |
PK_ZipCode |
1079674894 |
PK_DatabaseLog_DatabaseLogID |
1278627598 |
pk3 |
2103678542 |
SQL Server also supports the OBJECT_SCHEMA_NAME
function, which returns the name of the schema to which a schema-scoped object belongs. To use the function, you must pass in the object’s ID as an argument. Fortunately, you can nest metadata functions, as shown in the following example:
1 |
SELECT OBJECT_SCHEMA_NAME(OBJECT_ID(N'AdventureWorksDW2014.dbo.DimEmployee')); |
The SELECT
statement returns the name of the schema (Person
) for the DimEmployee
table in the AdventureWorksDW2014
database.
Another name-related metadata function is TYPE_NAME
, which can return the name of either a system type or a user-defined type, as shown in the following example:
1 2 3 4 5 |
SELECT name ColumnName, TYPE_NAME(system_type_id) SystemType, TYPE_NAME(user_type_id) UserType FROM sys.columns WHERE object_id = OBJECT_ID(N'Person.Person'); |
The SELECT
statement returns the following results:
ColumnName |
SystemType |
UserType |
BusinessEntityID |
int |
int |
PersonType |
nchar |
nchar |
NameStyle |
bit |
NameStyle |
Title |
nvarchar |
nvarchar |
FirstName |
nvarchar |
Name |
MiddleName |
nvarchar |
Name |
LastName |
nvarchar |
Name |
Suffix |
nvarchar |
nvarchar |
EmailPromotion |
int |
int |
AdditionalContactInfo |
xml |
xml |
Demographics |
xml |
xml |
rowguid |
uniqueidentifier |
uniqueidentifier |
ModifiedDate |
datetime |
datetime |
As with the ID-related functions, SQL Server also includes name-related metadata functions that you can call without specifying an argument. For example, the following SELECT
statement uses the DB_NAME
function to return the name of the current database:
1 |
SELECT DB_NAME(); |
When I ran the statement, it returned AdventureWorks2014
because that was the current database, but the function’s nondeterministic nature means that it will return whatever database you’re currently connected to.
Another name-related function is APP_NAME
, which returns the application name for the current session:
1 |
SELECT APP_NAME(); |
In my case, the function returned Microsoft SQL Server Management Studio - Query
. Keep in mind, however, that the client provides the application name and it is not verified. You should avoid implementing this function as part of your security strategy.
We can also use functions such as DB_NAME
and APP_NAME
for verification purposes. For example, we can use the DB_NAME
function to verify that the current database is the one we want, so we don’t inadvertently run commands against the wrong database:
1 2 3 4 5 |
IF DB_NAME() = 'AdventureWorks2014' PRINT 'This database is being accessed by ' + APP_NAME() + '.'; ELSE PRINT 'You are connected to the ' + DB_NAME() + ' database.'; |
Again, this is only a simply example, but it demonstrates how you can use these functions for verification purposes.
Note that you can also pass in an ID to the DB_NAME
function (unlike the APP_NAME
function), which you might need to do in certain applications:
1 |
SELECT DB_NAME(10); |
For certain functions, you sometimes have to put in a little extra effort to get the values you want. As we saw with the OBJECT_SCHEMA_NAME
function, we also included the OBJECT_ID
function to get the data we wanted.
Another function that requires more work is INDEX_COL
, which returns the column names that make up an index. The function takes three arguments: the table or view name, the index ID, and the index key column position. SQL Server provides no handy functions for supplying the values for the second and third arguments. However, we can get around this by going directly to the source, the sys.index_columns
view:
1 2 3 4 5 |
SELECT index_id, index_column_id, INDEX_COL('Person.Person', index_id, index_column_id) IndexCol FROM sys.index_columns WHERE object_id = OBJECT_ID('Person.Person') AND INDEX_COL('Person.Person', index_id, index_column_id) IS NOT NULL; |
When calling the INDEX_COL
function, I passed in the index_id
and index_column_id
column names as the second and third arguments. I also used the INDEX_COL
function in the WHERE
clause to filter out NULL
results. (The INDEX_COL
function returns NULL
for XML indexes, and I did not want to include them.) The SELECT
statement returns the following results:
IndexID |
ColumnID |
ColumnName |
1 |
1 |
BusinessEntityID |
2 |
1 |
LastName |
2 |
2 |
FirstName |
2 |
3 |
MiddleName |
3 |
1 |
rowguid |
SQL Server also includes name-related metadata functions for files and filegroups. The FILE_NAME
function returns the logical file name based on the file ID:
1 |
SELECT FILE_NAME(2); |
On my system, the statement returns AdventureWorks2014_Log
. The FILEGROUP_NAME
function works much the same way, this time returning MemOptFilegroup
:
1 |
SELECT FILEGROUP_NAME(2); |
There are a couple other name-related functions, but you get the idea. Now let’s look at how to retrieve property values.
Retrieving object property values
Let’s start with the OBJECTPROPERTY
function. As the name suggest, it returns property values for schema-scoped objects in the current database. When you call this function, you specify two arguments, the object’s ID and the name of the property, as shown in the following example:
1 |
SELECT OBJECTPROPERTY(OBJECT_ID(N'Production.Product'),'HasDeleteTrigger'); |
The SELECT
statement retrieves the value for the HasDeleteTrigger
property of the Production.Product
table. On my system, the statement returns 0
, or false, which means no delete trigger is defined on the table. In the next example, I retrieve the value of the IsUserTable
property:
1 |
SELECT OBJECTPROPERTY(OBJECT_ID(N'Production.Product'),'IsUserTable'); |
This time the statement returns 1
, or true, indicating that Product
is a user-defined table. The majority of properties return either true of false in this way, although there are plenty of exceptions. For example, the OwnerID
property shows the owner of the specified object:
1 |
SELECT OBJECTPROPERTY(OBJECT_ID(N'Production.Product'),'OwnerID'); |
On my system, the statement returns 1
, but this does not mean true. Rather, it is the ID of the dbo
security principal.
You can also use the property-related metadata functions for validation purposes, as we saw in earlier examples. For instance, the following T-SQL uses an IF
statement to determine whether the TableHasNonclusterIndex
property is set to true:
1 2 3 4 5 |
IF OBJECTPROPERTY(OBJECT_ID(N'Production.Product'), 'TableHasNonclustIndex') = 1 SELECT name, object_id FROM sys.indexes WHERE object_id = OBJECT_ID(N'Production.Product') AND type = 1; ELSE PRINT 'The table does not have a nonclustered index'; |
If the Product
table does not have a nonclustered index, the PRINT
statement will run. If it does have a clustered index, the SELECT
statement will run and return the following results:
name |
object_id |
PK_Product_ProductID |
1973582069 |
SQL Server also supports a number of other property-related metadata function. For example, the following SELECT
statement uses the INDEXPROPERTY
function to determine whether an index has been disabled:
1 |
SELECT INDEXPROPERTY(OBJECT_ID(N'Production.Product'),'AK_Product_ProductNumber','IsDisabled'); |
Unlike the the OBJECTPROPERTY
function, the the INDEXPROPERTY
function takes three arguments: the object, the index name, and the property. (Note that you can specify a statistics name rather than an index name.)
SQL Server supports a number of property-related metadata functions and for each function supports a number of properties, too many to cover here. But let’s look at a few more simple examples to give you a sense of the amount of information available to you.
The following SELECT
statement uses the DATABASEPROPERTYEX
function with the Collation
property to retrieve the default collation for the AdventureWorks2014
database:
1 |
SELECT DATABASEPROPERTYEX('AdventureWorks2014','Collation'); |
The next example uses the Status
property to check whether the database is online, offline, recovering, or one of several other conditions:
1 |
SELECT DATABASEPROPERTYEX('AdventureWorks2014','Status'); |
SQL Server also includes the SERVERPROPERTY
function for gathering information about the current server instance. For example, the following SELECT
statement retrieves the NetBIOS computer name:
1 |
SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS'); |
Or you can use the function to retrieve the current SQL Server edition:
1 |
SELECT SERVERPROPERTY('Edition'); |
Another handy property-related function is TYPEPROPERTY
, which returns a data type’s property values. The following example uses the function to find the value of the Precision
property:
1 |
SELECT TYPEPROPERTY('bigint','Precision'); |
The statement returns a value of which is 19
.
There are so many properties from which to choose that you’ll have to rely on SQL Server documentation to look them up when you need them, unless you have already memorized the ones you use most often. The properties are listed with each function’s help topic, so start there if you want to retrieve property values.
More fun metadata functions
The types of metadata functions we’ve looked at so far have generally fallen into three categories: IDs, names, and properties. However, SQL Server supports several other metadata functions that don’t quite fit into these categories. For example, you can use the the COL_LENGTH
function to retrieve a column’s length in bytes:
1 |
SELECT COL_LENGTH('dbo.Products','ProductName'); |
Be aware when using the function that the length refers to total bytes, not necessarily the specified number of bytes in the column definition. In this case, the Product
name column is defined with the NVARCHAR(50)
type, but the SELECT
statement actually returns a value of 100
because of the type’s two-byte, Unicode nature.
As with the other metadata functions we’ve looked at, you can use the the COL_LENGTH
function to test for a condition before proceeding with an operation. For example, you might use the function to check for a column’s existence, similar to how you can use the OBJECT_ID
function:
1 2 3 4 5 |
IF COL_LENGTH('dbo.Products', 'ProductName') IS NOT NULL INSERT dbo.Products (ProductName) VALUES('widget1'); ELSE PRINT 'The ProductName column does not exist.' |
SQL Server also provides the OBJECT_DEFINITION
function for retrieving the T-SQL code that defines an object:
1 |
SELECT OBJECT_DEFINITION(OBJECT_ID('HumanResources.uspUpdateEmployeeLogin')) |
In this case, the SELECT
statement will return the CREATE
statement used to generate the uspUpdateEmployeeLogin
stored procedure:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
CREATE PROCEDURE [HumanResources].[uspUpdateEmployeeLogin] @BusinessEntityID [int], @OrganizationNode [hierarchyid], @LoginID [nvarchar](256), @JobTitle [nvarchar](50), @HireDate [datetime], @CurrentFlag [dbo].[Flag] WITH EXECUTE AS CALLER AS BEGIN SET NOCOUNT ON; BEGIN TRY UPDATE [HumanResources].[Employee] SET [OrganizationNode] = @OrganizationNode ,[LoginID] = @LoginID ,[JobTitle] = @JobTitle ,[HireDate] = @HireDate ,[CurrentFlag] = @CurrentFlag WHERE [BusinessEntityID] = @BusinessEntityID; END TRY BEGIN CATCH EXECUTE [dbo].[uspLogError]; END CATCH; END; |
We can just as easily retrieve the definitions for other types of objects, such as views:
1 |
SELECT OBJECT_DEFINITION(OBJECT_ID('Sales.vSalesPerson')) |
The OBJECT_DEFINITION
function provides a quick and easy way to retrieve the T-SQL code when we want it. We only need to provide the object ID, made available once again through the use of the OBJECT_ID
function.
Another interesting metadata function is STATS_DATE
, which returns the data of the most recent statistics update on a table or indexed view:
1 2 3 |
SELECT name, stats_id,STATS_DATE(object_id, stats_id) StatsDate FROM sys.stats WHERE object_id= OBJECT_ID('Person.Person'); |
When calling the function, we need to provide the object ID of the table or view and the statistics ID, which are readily available through the sys.stats
catalog view. The SELECT
statement returns the following results:
name |
stats_id |
StatsDate |
PK_Person_BusinessEntityID |
1 |
2014-07-17 16:11:30.067 |
IX_Person_LastName_FirstName_MiddleName |
2 |
2014-07-17 16:11:31.783 |
AK_Person_rowguid |
3 |
2014-07-17 16:11:31.807 |
_WA_Sys_00000004_693CA210 |
4 |
2015-06-05 11:37:38.303 |
_WA_Sys_00000002_693CA210 |
5 |
2015-08-04 17:31:51.280 |
If you’re using sequence objects in your database, you can use the NEXT VALUE FOR
metadata function to generate the next number for the specified sequence:
1 |
SELECT NEXT VALUEFOR AdventureWorks2014.dbo.AWSequence; |
You need only provide the sequence name when calling the function, qualified as necessary. For example, if you define a sequence that starts at 101 and increments by 1, the first time you call the function, you’ll receive the value 101
, the second time, the value 102
, and so on.
Now let’s look at one more metadata function: SCOPE_IDENTITY
. The function returns that last value inserted into an IDENTITY
column within the same scope as the statement that calls the function. In this case, scope refers to a module such as a stored procedure, function, or batch. For example, the following T-SQL creates the Products
table, then creates the InsertProducts
stored procedure, which includes the SCOPE_IDENTITY
function, and finally runs an EXEC
statement against the stored procedure:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
IF OBJECT_ID (N'dbo.Products', N'U') IS NOT NULL DROP TABLE dbo.Products; GO CREATE TABLE dbo.Products (ProductID INT PRIMARY KEY IDENTITY(101, 1), ProductName NVARCHAR(50) NOT NULL); GO IF OBJECT_ID (N'dbo.InsertProduct', N'P') IS NOT NULL DROP PROCEDURE dbo.InsertProduct; GO CREATE PROCEDURE dbo.InsertProduct (@ProdName VARCHAR(50) = NULL) AS IF @ProdName IS NOT NULL BEGIN INSERT INTO dbo.Products(ProductName) VALUES(@ProdName); SELECT SCOPE_IDENTITY(); END EXEC InsertProduct 'widget2'; |
After the stored procedure’s INSERT
statement runs, a SELECT
statement calls the SCOPE_IDENTITY
function. When we run the EXEC
statement for the first time, the SELECT
statement returns a value of 101
(our first IDENTITY
value) and continues from there, incrementing by one each time we add a row. Returning the IDENTITY
value in this way can be useful when you want to log events, insert data into other tables, run triggers, or carry out other operations that might rely on that value.
SQL Server metadata functions
In this article, we’ve looked at a number of SQL Server metadata functions and the variety of information they can return, whether object IDs or names, their properties, or other types of data. SQL Server also includes a few other metadata functions, but the examples we’ve looked at here cover the majority of them and should give you a good sense of how they work.
As you dig into the metadata functions, you’ll find that leaning about them is well worth the effort. You might be surprised how useful they can be and how you’ll come to rely on that. The better you understand what metadata functions are available and how they work, the better you can take advantage of them in your T-SQL code and SQL Server applications.
Load comments