{"id":2184,"date":"2016-03-09T00:00:00","date_gmt":"2016-03-09T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/sql-server-metadata-functions-the-basics\/"},"modified":"2021-09-29T16:21:21","modified_gmt":"2021-09-29T16:21:21","slug":"sql-server-metadata-functions-the-basics","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/sql-server-metadata-functions-the-basics\/","title":{"rendered":"SQL Server Metadata Functions: The Basics"},"content":{"rendered":"
\n

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.<\/p>\n

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. <\/p>\n

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<\/code><\/b> statement.<\/p>\n

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<\/code><\/b> clause to retrieve a specific bit of information.<\/p>\n

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<\/code><\/b> or AdventureWorksDW2014<\/code><\/b> 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.<\/p>\n

Retrieving object IDs<\/h1>\n

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. <\/p>\n

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<\/code><\/b>, which returns the database identifier for the specified object. <\/p>\n

For example, on my instance of SQL Server, the following SELECT<\/code><\/b> statement returns 1237579447<\/code><\/b> as the identifier for the HumanResources.Employee<\/code><\/b> table in the AdventureWorks2014<\/code><\/b> database:<\/p>\n

SELECT OBJECT_ID(N'AdventureWorks2014.HumanResources.Employee');<\/pre>\n
When calling  the the  OBJECT_ID<\/code><\/b> function, we must ensure that we  target the correct object and<\/i> the  correct database. However, if the  AdventureWorks2014<\/code><\/b>  database is already the active database, we can drop its name from the argument  we pass into the function:<\/p>\n
USE AdventureWorks2014;\nGO\n \nSELECT OBJECT_ID(N'HumanResources.Employee');\n\t<\/pre>\n
You can use  the  OBJECT_ID<\/code><\/b> 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<\/code><\/b>, 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)<\/a>.<\/p>\n
One of the  handiest uses of the  OBJECT_ID<\/code><\/b> 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<\/code><\/b>  statement to test whether the  OBJECT_ID<\/code><\/b> function  returns a value for the  dbo.EmployeeInfo<\/code><\/b>  table:<\/p>\n
IF OBJECT_ID(N'dbo.EmployeeInfo', N'U') IS NOT NULL\nDROP TABLE dbo.EmployeeInfo;\nGO\n\t<\/pre>\n
If the table  exists, the function will return the identifier and the DROP<\/code><\/b>  TABLE<\/code><\/b> statement will run. If the table does not exist, the  function will return a  NULL<\/code><\/b> value and the DROP<\/code><\/b>  TABLE<\/code><\/b> statement will not run.<\/p>\n
When working  with system views, you will often need to use the OBJECT_ID<\/code><\/b>  function because the view does not include the object’s name. For example, the  sys.columns<\/code><\/b> 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<\/code><\/b> clause in  your query that filters on the table’s object ID, as shown in the following  example:<\/p>\n
SELECT name, column_id\nFROM sys.columns\nWHERE object_id = OBJECT_ID(N'Person.ContactType');\n<\/pre>\n
As is often  the case, we know an object’s name, but not its ID. However, rather than having  to join the  sys.columns<\/code><\/b> view to the sys.objects<\/code><\/b>  view to get the object name, we can simply use the OBJECT_ID<\/code><\/b>  function, giving us the following results:<\/p>\n\n\n\n\n\n\n
\n
 \t\tname<\/b><\/p>\n<\/td>\n
\n
 \t\tcolumn_id<\/b><\/p>\n<\/td>\n<\/tr>\n
\n
ContactTypeID<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n<\/tr>\n
\n
Name<\/p>\n<\/td>\n
\n
2<\/p>\n<\/td>\n<\/tr>\n
\n
ModifiedDate<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
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<\/code><\/b> for the  Person<\/code><\/b> schema. We can use the SCHEMA_ID<\/code><\/b>  function to pass in the schema name, rather than having to know its ID:<\/p>\n
SELECT name, max_column_id_used\nFROM sys.tables\nWHERE schema_id = SCHEMA_ID(N'Person');\n\t<\/pre>\n
The  SCHEMA_ID<\/code><\/b> function works just like the OBJECT_ID<\/code><\/b>  function in terms of saving us from having to join the table or look up the ID.  The  SELECT<\/code><\/b> statement returns the following results:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
\n
 \t\tname<\/b><\/p>\n<\/td>\n
\n
 \t\tmax_column_id_used<\/b><\/p>\n<\/td>\n<\/tr>\n
\n
Address<\/p>\n<\/td>\n
\n
9<\/p>\n<\/td>\n<\/tr>\n
\n
AddressType<\/p>\n<\/td>\n
\n
4<\/p>\n<\/td>\n<\/tr>\n
\n
StateProvince<\/p>\n<\/td>\n
\n
8<\/p>\n<\/td>\n<\/tr>\n
\n
BusinessEntity<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n<\/tr>\n
\n
BusinessEntityAddress<\/p>\n<\/td>\n
\n
5<\/p>\n<\/td>\n<\/tr>\n
\n
BusinessEntityContact<\/p>\n<\/td>\n
\n
5<\/p>\n<\/td>\n<\/tr>\n
\n
ContactType<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n<\/tr>\n
\n
CountryRegion<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n<\/tr>\n
\n
EmailAddress<\/p>\n<\/td>\n
\n
5<\/p>\n<\/td>\n<\/tr>\n
\n
Password<\/p>\n<\/td>\n
\n
5<\/p>\n<\/td>\n<\/tr>\n
\n
Person<\/p>\n<\/td>\n
\n
13<\/p>\n<\/td>\n<\/tr>\n
\n
PersonPhone<\/p>\n<\/td>\n
\n
4<\/p>\n<\/td>\n<\/tr>\n
\n
PhoneNumberType<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
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<\/code><\/b> function.  The  WHERE<\/code><\/b> clause in the following SELECT<\/code><\/b>  statement includes the  OBJECT_ID<\/code><\/b> and  TYPE_ID<\/code><\/b> functions in order to limit the results:<\/p>\n
SELECT name, is_nullable\nFROM sys.columns\nWHERE object_id = OBJECT_ID(N'Person.Person')\n  AND user_type_id = TYPE_ID(N'name');\n\t<\/pre>\n
The  WHERE<\/code><\/b> clause uses the  OBJECT_ID<\/code><\/b> function  to return the ID of the  Person.Person<\/code><\/b>  table and uses the  TYPE_ID<\/code><\/b> function to return the ID of the  Name<\/code><\/b> user-defined data type, giving us the following  results:<\/b><\/p>\n\n\n\n\n\n\n
\n
 \t\tname<\/b><\/p>\n<\/td>\n
\n
 \t\tis_nullable<\/b><\/p>\n<\/td>\n<\/tr>\n
\n
FirstName<\/p>\n<\/td>\n
\n
0<\/p>\n<\/td>\n<\/tr>\n
\n
MiddleName<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n<\/tr>\n
\n
LastName<\/p>\n<\/td>\n
\n
0<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
There is  even a metadata function for retrieving the ID of a security principal in the  current database, as shown in the following example:<\/p>\n
SELECT name, schema_id\nFROM sys.schemas\nWHERE principal_id = DATABASE_PRINCIPAL_ID(N'dbo');\n<\/pre>\n
In this  case, the  DATABASE_PRINCIPAL_ID<\/code><\/b> function returns the ID of  the  dbo<\/code><\/b> principal, which is associated with the  schemas shown in the following results:<\/p>\n\n\n\n\n\n\n\n\n\n
\n
 \t\tname<\/b><\/p>\n<\/td>\n
\n
 \t\tschema_id<\/b><\/p>\n<\/td>\n<\/tr>\n
\n
dbo<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n<\/tr>\n
\n
HumanResources<\/p>\n<\/td>\n
\n
5<\/p>\n<\/td>\n<\/tr>\n
\n
Person<\/p>\n<\/td>\n
\n
6<\/p>\n<\/td>\n<\/tr>\n
\n
Production<\/p>\n<\/td>\n
\n
7<\/p>\n<\/td>\n<\/tr>\n
\n
Purchasing<\/p>\n<\/td>\n
\n
8<\/p>\n<\/td>\n<\/tr>\n
\n
Sales<\/p>\n<\/td>\n
\n
9<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
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<\/code><\/b> function can return the ID of the  current database without specifying the name of that database:<\/p>\n
SELECT database_id, compatibility_level, collation_name\nFROM sys.databases\nWHERE database_id = DB_ID();\n\t<\/pre>\n
The  statement uses the function to narrow down the data from the sys.databases<\/code><\/b>  catalog view, without having to specify the database name, giving us the  following results:<\/p>\n\n\n\n\n
\n
 \t\tdatabase_id<\/b><\/p>\n<\/td>\n
\n
 \t\tcompatibility_level<\/b><\/p>\n<\/td>\n
\n
 \t\tcollation_name<\/b><\/p>\n<\/td>\n<\/tr>\n
\n
9<\/p>\n<\/td>\n
\n
120<\/p>\n<\/td>\n
\n
 \t\tSQL_Latin1_General_CP1_CI_AS<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
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<\/code><\/b> function to verify whether an  operation should be carried out, based on the current user (principal):<\/p>\n
IF DATABASE_PRINCIPAL_ID() = 1\n  PRINT 'The trigger will fire.';\nELSE\n  PRINT 'The trigger will not file.';\n\t<\/pre>\n
In this  case, I’m using  PRINT<\/code><\/b> 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<\/code><\/b>).<\/p>\n
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<\/code><\/b> function  to return the ID for the specified logical file, in this case,  AdventureWorks2014_Log<\/code><\/b>:<\/p>\n
SELECT FILE_IDEX('AdventureWorks2014_Log');\n\t<\/pre>\n
Note that  SQL Server also supports the  FILE_ID<\/code><\/b> function,  but that has been deprecated and will be removed in a future version of SQL  Server.<\/p>\n
If you  instead want to retrieve a filegroup’s ID, you can use the FILEGROUP_ID<\/code><\/b>  function:<\/p>\n
SELECT FILEGROUP_ID('MemOptFilegroup')\n\t<\/pre>\n
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.<\/p>\n
Retrieving object names<\/h1>\n
At the top  of the list of name-related functions is  OBJECT_NAME<\/code><\/b>, which  retrieves the name of a schema-scoped object, based on the specified object ID.<\/p>\n
The  following example uses the  OBJECT_NAME<\/code><\/b> function in the  SELECT<\/code><\/b> clause to return the names of specific tables and  views:<\/p>\n
SELECT OBJECT_NAME(object_id) TableName\nFROM sys.columns\nWHERE user_type_id = TYPE_ID(N'name') AND is_nullable = 1\nGROUP BY object_id\nORDER BY TableName;\n\t<\/pre>\n
The  SELECT<\/code><\/b> statement returns the tables and views that contain  nullable columns configured with the  Name<\/code><\/b> user-defined  data type, giving us the following results:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n
\n
 \t\tTableName<\/b><\/p>\n<\/td>\n<\/tr>\n
\n
Person<\/p>\n<\/td>\n<\/tr>\n
\n
 \t\tvAdditionalContactInfo<\/p>\n<\/td>\n<\/tr>\n
\n
vEmployee<\/p>\n<\/td>\n<\/tr>\n
\n
vEmployeeDepartment<\/p>\n<\/td>\n<\/tr>\n
\n
 \t\tvEmployeeDepartmentHistory<\/p>\n<\/td>\n<\/tr>\n
\n
vIndividualCustomer<\/p>\n<\/td>\n<\/tr>\n
\n
vSalesPerson<\/p>\n<\/td>\n<\/tr>\n
\n
vStoreWithContacts<\/p>\n<\/td>\n<\/tr>\n
\n
vVendorWithContacts<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Functions  such as  OBJECT_NAME<\/code><\/b> 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<\/code><\/b> clause.<\/p>\n
Consider the  following  SELECT<\/code><\/b> statement, which retrieves data from the  sys.columns<\/code><\/b> view in the AdventureWorksDW2014<\/code><\/b>  database, but from within the context of the AdventureWorks2014<\/code><\/b>  database:<\/p>\n
USE AdventureWorks2014;\nGO\n \nSELECT OBJECT_NAME(object_id) TableCurrentDB, \n  OBJECT_NAME(object_id, DB_ID(N'AdventureWorksDW2014')) TableRefDB, \n  name ColumnName \nFROM AdventureWorksDW2014.sys.columns\nWHERE object_id = 677577452;\n\t<\/pre>\n
  The first  two expressions in the  SELECT<\/code><\/b> clause use  the  OBJECT_NAME<\/code><\/b> function to retrieve the object name based on  the value in the  object_id<\/code><\/b> column of the target table.  However, the second instance of the function also passes in the database ID,  giving us the following results:<\/p>\n\n\n\n\n\n\n
\n
 \t\tTableCurrentDB<\/b><\/p>\n<\/td>\n
\n
 \t\tTableRefDB<\/b><\/p>\n<\/td>\n
\n
 \t\tColumnName<\/b><\/p>\n<\/td>\n<\/tr>\n
\n
BusinessEntityAddress<\/p>\n<\/td>\n
\n
 \t\tFactInternetSalesReason<\/p>\n<\/td>\n
\n
SalesOrderNumber<\/p>\n<\/td>\n<\/tr>\n
\n
BusinessEntityAddress<\/p>\n<\/td>\n
\n
 \t\tFactInternetSalesReason<\/p>\n<\/td>\n
\n
SalesOrderLineNumber<\/p>\n<\/td>\n<\/tr>\n
\n
BusinessEntityAddress<\/p>\n<\/td>\n
\n
 \t\tFactInternetSalesReason<\/p>\n<\/td>\n
\n
SalesReasonKey<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Because we  do not specify the database in the first function instance, the returned  information is actually coming from the  AdventureWorks2014<\/code><\/b>  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.<\/p>\n
SQL Server  also provides the  SCHEMA_NAME<\/code><\/b>  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<\/code><\/b> statement  uses the function in the  WHERE<\/code><\/b> clause,  taking the ID from the  schema_id<\/code><\/b> column:<\/p>\n
SELECT name PrimaryKey, object_id ObjectID\nFROM sys.objects\nWHERE SCHEMA_NAME(schema_id) = 'dbo' AND type = 'PK'; \n\t<\/pre>\n
The  statement uses the  SCHEMA_NAME<\/code><\/b> function to return information about  the primary keys within the  dbo<\/code><\/b> schema, giving  us the following results:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n
\n
 \t\tPrimaryKey<\/b><\/p>\n<\/td>\n
\n
 \t\tObjectID<\/b><\/p>\n<\/td>\n<\/tr>\n
\n
pk33<\/p>\n<\/td>\n
\n
52195236<\/p>\n<\/td>\n<\/tr>\n
\n
 \t\tAdvisorTest3_primaryKey<\/p>\n<\/td>\n
\n
84195350<\/p>\n<\/td>\n<\/tr>\n
\n
 \t\tPK_ErrorLog_ErrorLogID<\/p>\n<\/td>\n
\n
309576141<\/p>\n<\/td>\n<\/tr>\n
\n
 \t\tPK__LookupTe__6D8B9C6BA3B225AC<\/p>\n<\/td>\n
\n
532196946<\/p>\n<\/td>\n<\/tr>\n
\n
 \t\tPK__AdvisorT__8CC3310032114505<\/p>\n<\/td>\n
\n
564197060<\/p>\n<\/td>\n<\/tr>\n
\n
 \t\tPK_AWBuildVersion_SystemInformationID<\/p>\n<\/td>\n
\n
1070626857<\/p>\n<\/td>\n<\/tr>\n
\n
PK_ZipCode<\/p>\n<\/td>\n
\n
1079674894<\/p>\n<\/td>\n<\/tr>\n
\n
 \t\tPK_DatabaseLog_DatabaseLogID<\/p>\n<\/td>\n
\n
1278627598<\/p>\n<\/td>\n<\/tr>\n
\n
pk3<\/p>\n<\/td>\n
\n
2103678542<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
SQL Server  also supports the  OBJECT_SCHEMA_NAME<\/code><\/b>  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:<\/p>\n
SELECT OBJECT_SCHEMA_NAME(OBJECT_ID(N'AdventureWorksDW2014.dbo.DimEmployee'));\n\t<\/pre>\n
The  SELECT<\/code><\/b> statement returns the name of the schema (Person<\/code><\/b>) for the  DimEmployee<\/code><\/b> table  in the  AdventureWorksDW2014<\/code><\/b> database.<\/p>\n
Another  name-related metadata function is  TYPE_NAME<\/code><\/b>, which  can return the name of either a system type or a user-defined type, as shown in  the following example:<\/p>\n
SELECT name ColumnName,\n  TYPE_NAME(system_type_id) SystemType,\n  TYPE_NAME(user_type_id) UserType\nFROM sys.columns\nWHERE object_id = OBJECT_ID(N'Person.Person');\n\t<\/pre>\n
The  SELECT<\/code><\/b> statement returns the following results:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
\n
 \t\tColumnName<\/b><\/p>\n<\/td>\n
\n
 \t\tSystemType<\/b><\/p>\n<\/td>\n
\n
 \t\tUserType<\/b><\/p>\n<\/td>\n<\/tr>\n
\n
BusinessEntityID<\/p>\n<\/td>\n
\n
int<\/p>\n<\/td>\n
\n
int<\/p>\n<\/td>\n<\/tr>\n
\n
PersonType<\/p>\n<\/td>\n
\n
nchar<\/p>\n<\/td>\n
\n
nchar<\/p>\n<\/td>\n<\/tr>\n
\n
NameStyle<\/p>\n<\/td>\n
\n
bit<\/p>\n<\/td>\n
\n
NameStyle<\/p>\n<\/td>\n<\/tr>\n
\n
Title<\/p>\n<\/td>\n
\n
nvarchar<\/p>\n<\/td>\n
\n
nvarchar<\/p>\n<\/td>\n<\/tr>\n
\n
FirstName<\/p>\n<\/td>\n
\n
nvarchar<\/p>\n<\/td>\n
\n
Name<\/p>\n<\/td>\n<\/tr>\n
\n
MiddleName<\/p>\n<\/td>\n
\n
nvarchar<\/p>\n<\/td>\n
\n
Name<\/p>\n<\/td>\n<\/tr>\n
\n
LastName<\/p>\n<\/td>\n
\n
nvarchar<\/p>\n<\/td>\n
\n
Name<\/p>\n<\/td>\n<\/tr>\n
\n
Suffix<\/p>\n<\/td>\n
\n
nvarchar<\/p>\n<\/td>\n
\n
nvarchar<\/p>\n<\/td>\n<\/tr>\n
\n
EmailPromotion<\/p>\n<\/td>\n
\n
int<\/p>\n<\/td>\n
\n
int<\/p>\n<\/td>\n<\/tr>\n
\n
AdditionalContactInfo<\/p>\n<\/td>\n
\n
xml<\/p>\n<\/td>\n
\n
xml<\/p>\n<\/td>\n<\/tr>\n
\n
Demographics<\/p>\n<\/td>\n
\n
xml<\/p>\n<\/td>\n
\n
xml<\/p>\n<\/td>\n<\/tr>\n
\n
rowguid<\/p>\n<\/td>\n
\n
uniqueidentifier<\/p>\n<\/td>\n
\n
uniqueidentifier<\/p>\n<\/td>\n<\/tr>\n
\n
ModifiedDate<\/p>\n<\/td>\n
\n
datetime<\/p>\n<\/td>\n
\n
datetime<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
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<\/code><\/b> statement uses the DB_NAME<\/code><\/b>  function to return the name of the current database:<\/p>\n
SELECT DB_NAME();<\/pre>\n
When I ran  the statement, it returned  AdventureWorks2014<\/code><\/b>  because that was the current database, but the function’s nondeterministic  nature means that it will return whatever database you’re currently connected  to. <\/p>\n
Another  name-related function is  APP_NAME<\/code><\/b>, which  returns the application name for the current session:<\/b><\/p>\n
SELECT APP_NAME();<\/pre>\n
In my case,  the function returned  Microsoft SQL Server  Management Studio - Query<\/code><\/b>. 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.<\/p>\n
We can also  use functions such as  DB_NAME<\/code><\/b> and  APP_NAME<\/code><\/b> for verification purposes. For example, we can use  the  DB_NAME<\/code><\/b> function to verify that the current  database is the one we want, so we don’t inadvertently run commands against the  wrong database:<\/p>\n
IF DB_NAME() = 'AdventureWorks2014'\n  PRINT 'This database is being accessed by ' + APP_NAME() + '.';\nELSE\n  PRINT 'You are connected to the ' + DB_NAME() + ' database.';\n \n<\/pre>\n
Again, this  is only a simply example, but it demonstrates how you can use these functions  for verification purposes.<\/p>\n
Note that  you can also pass in an ID to the  DB_NAME<\/code><\/b> function  (unlike the  APP_NAME<\/code><\/b> function), which you might need to do  in certain applications:<\/p>\n
SELECT DB_NAME(10);<\/pre>\n
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<\/code><\/b>  function, we also included the  OBJECT_ID<\/code><\/b> function  to get the data we wanted.<\/p>\n
Another  function that requires more work is  INDEX_COL<\/code><\/b>, 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<\/code><\/b>  view: <\/p>\n
SELECT index_id, index_column_id, \n  INDEX_COL('Person.Person', index_id, index_column_id) IndexCol\nFROM sys.index_columns\nWHERE object_id = OBJECT_ID('Person.Person')\n  AND INDEX_COL('Person.Person', index_id, index_column_id) IS NOT NULL;\n\t<\/pre>\n
When calling  the  INDEX_COL<\/code><\/b> function, I passed in the  index_id <\/code><\/b>and  index_column_id<\/code><\/b>  column names as the second and third arguments. I also used the INDEX_COL<\/code><\/b>  function in the  WHERE<\/code><\/b> clause to filter out  NULL<\/code><\/b> results. (The  INDEX_COL<\/code><\/b> function  returns  NULL<\/code><\/b> for XML indexes, and I did not want to  include them.) The  SELECT<\/code><\/b> statement  returns the following results:<\/p>\n\n\n\n\n\n\n\n\n
\n
 \t\tIndexID<\/b><\/p>\n<\/td>\n
\n
 \t\tColumnID<\/b><\/p>\n<\/td>\n
\n
 \t\tColumnName<\/b><\/p>\n<\/td>\n<\/tr>\n
\n
1<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n
\n
BusinessEntityID<\/p>\n<\/td>\n<\/tr>\n
\n
2<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n
\n
LastName<\/p>\n<\/td>\n<\/tr>\n
\n
2<\/p>\n<\/td>\n
\n
2<\/p>\n<\/td>\n
\n
FirstName<\/p>\n<\/td>\n<\/tr>\n
\n
2<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n
\n
MiddleName<\/p>\n<\/td>\n<\/tr>\n
\n
3<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n
\n
rowguid<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
SQL Server  also includes name-related metadata functions for files and filegroups. The  FILE_NAME<\/code><\/b> function returns the logical file name based on  the file ID:<\/p>\n
SELECT FILE_NAME(2);<\/pre>\n
On my  system, the statement returns  AdventureWorks2014_Log<\/code><\/b>.  The  FILEGROUP_NAME<\/code><\/b> function works much the same way,  this time returning  MemOptFilegroup<\/code><\/b>:<\/p>\n
SELECT FILEGROUP_NAME(2);<\/pre>\n
There are a  couple other name-related functions, but you get the idea. Now let’s look at how  to retrieve property values.<\/p>\n
Retrieving object property  values<\/h1>\n
Let’s start  with the  OBJECTPROPERTY<\/code><\/b> 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:<\/p>\n
SELECT OBJECTPROPERTY(OBJECT_ID(N'Production.Product'),'HasDeleteTrigger');<\/pre>\n
The  SELECT<\/code><\/b> statement retrieves the value for the  HasDeleteTrigger<\/code><\/b> property of the Production.Product<\/code><\/b>  table. On my system, the statement returns  0<\/code><\/b>, or false, which  means no delete trigger is defined on the table. In the next example, I retrieve  the value of the  IsUserTable<\/code><\/b>  property:<\/p>\n
SELECT OBJECTPROPERTY(OBJECT_ID(N'Production.Product'),'IsUserTable');<\/pre>\n
This time  the statement returns  1<\/code><\/b>, or true,  indicating that  Product<\/code><\/b> 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<\/code><\/b> property  shows the owner of the specified object:<\/p>\n
SELECT OBJECTPROPERTY(OBJECT_ID(N'Production.Product'),'OwnerID');<\/pre>\n
On my  system, the statement returns  1<\/code><\/b>, but this does  not mean true. Rather, it is the ID of the  dbo<\/code><\/b> security  principal.<\/p>\n
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<\/code><\/b> statement to determine whether the  TableHasNonclusterIndex<\/code><\/b> property is set to true:<\/p>\n
IF OBJECTPROPERTY(OBJECT_ID(N'Production.Product'), 'TableHasNonclustIndex') = 1\n  SELECT name, object_id FROM sys.indexes\n  WHERE object_id = OBJECT_ID(N'Production.Product') AND type = 1;\nELSE\n  PRINT 'The table does not have a nonclustered index';\n\t<\/pre>\n
If the  Product<\/code><\/b> table does not have a nonclustered index, the  PRINT<\/code><\/b> statement will run. If it does have a clustered index,  the  SELECT<\/code><\/b> statement will run and return the  following results:<\/p>\n\n\n\n\n
\n
 \t\tname<\/b><\/p>\n<\/td>\n
\n
 \t\tobject_id<\/b><\/p>\n<\/td>\n<\/tr>\n
\n
PK_Product_ProductID<\/p>\n<\/td>\n
\n
1973582069<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
SQL Server  also supports a number of other property-related metadata function. For example,  the following  SELECT<\/code><\/b> statement  uses the  INDEXPROPERTY<\/code><\/b> function to determine whether an  index has been disabled:<\/p>\n
SELECT INDEXPROPERTY(OBJECT_ID(N'Production.Product'),'AK_Product_ProductNumber','IsDisabled');<\/pre>\n
Unlike the  the  OBJECTPROPERTY<\/code><\/b> function, the the  INDEXPROPERTY<\/code><\/b> 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.)<\/b><\/p>\n
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.<\/p>\n
The  following  SELECT<\/code><\/b> statement uses the DATABASEPROPERTYEX<\/code><\/b>  function with the  Collation<\/code><\/b> property  to retrieve the default collation for the  AdventureWorks2014<\/code><\/b>  database:<\/p>\n
SELECT DATABASEPROPERTYEX('AdventureWorks2014','Collation');<\/pre>\n
The next  example uses the  Status<\/code><\/b> property to check whether the  database is online, offline, recovering, or one of several other conditions:<\/p>\n
SELECT DATABASEPROPERTYEX('AdventureWorks2014','Status');<\/pre>\n
SQL Server  also includes the  SERVERPROPERTY<\/code><\/b>  function for gathering information about the current server instance. For  example, the following  SELECT<\/code><\/b> statement retrieves the NetBIOS computer  name:<\/p>\n
SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS');<\/pre>\n
Or you can  use the function to retrieve the current SQL Server edition:<\/p>\n
SELECT SERVERPROPERTY('Edition');<\/pre>\n
Another  handy property-related function is  TYPEPROPERTY<\/code><\/b>,  which returns a data type’s property values. The following example uses the  function to find the value of the  Precision<\/code><\/b> property:<\/p>\n
SELECT TYPEPROPERTY('bigint','Precision');<\/pre>\n
The  statement returns a value of which is  19<\/code><\/b>.  <\/p>\n
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.<\/p>\n
More fun metadata functions<\/h1>\n
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<\/code><\/b> function to retrieve a  column’s length in bytes:<\/p>\n
SELECT COL_LENGTH('dbo.Products','ProductName'); <\/pre>\n
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<\/code><\/b> name column is defined with the  NVARCHAR(50)<\/code><\/b> type, but the SELECT<\/code><\/b>  statement actually returns a value of  100<\/code><\/b> because of the  type’s two-byte, Unicode nature.<\/p>\n
As with the  other metadata functions we’ve looked at, you can use the the COL_LENGTH<\/code><\/b>  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<\/code><\/b>  function:<\/p>\n
IF COL_LENGTH('dbo.Products', 'ProductName') IS NOT NULL\n  INSERT dbo.Products (ProductName)\n  VALUES('widget1'); \nELSE\n  PRINT 'The ProductName column does not exist.'\n\t<\/pre>\n
  SQL Server  also provides the  OBJECT_DEFINITION<\/code><\/b>  function for retrieving the T-SQL code that defines an object:<\/p>\n
SELECT OBJECT_DEFINITION(OBJECT_ID('HumanResources.uspUpdateEmployeeLogin'))<\/pre>\n
In this  case, the  SELECT<\/code><\/b> statement will return the  CREATE<\/code><\/b> statement used to generate the  uspUpdateEmployeeLogin<\/code><\/b> stored procedure:<\/p>\n
CREATE PROCEDURE [HumanResources].[uspUpdateEmployeeLogin]\n    @BusinessEntityID [int], \n    @OrganizationNode [hierarchyid],\n    @LoginID [nvarchar](256),\n    @JobTitle [nvarchar](50),\n    @HireDate [datetime],\n    @CurrentFlag [dbo].[Flag]\nWITH EXECUTE AS CALLER\nAS\nBEGIN\n    SET NOCOUNT ON;\n \n    BEGIN TRY\n        UPDATE [HumanResources].[Employee] \n        SET [OrganizationNode] = @OrganizationNode \n            ,[LoginID] = @LoginID \n            ,[JobTitle] = @JobTitle \n            ,[HireDate] = @HireDate \n            ,[CurrentFlag] = @CurrentFlag \n        WHERE [BusinessEntityID] = @BusinessEntityID;\n    END TRY\n    BEGIN CATCH\n        EXECUTE [dbo].[uspLogError];\n    END CATCH;\nEND;\n \n<\/pre>\n
We can just  as easily retrieve the definitions for other types of objects, such as views:<\/p>\n
SELECT OBJECT_DEFINITION(OBJECT_ID('Sales.vSalesPerson'))<\/pre>\n
The  OBJECT_DEFINITION<\/code><\/b> 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<\/code><\/b>  function. <\/p>\n
Another  interesting metadata function is  STATS_DATE<\/code><\/b>, which  returns the data of the most recent statistics update on a table or indexed  view:<\/p>\n
SELECT name, stats_id,STATS_DATE(object_id, stats_id) StatsDate\n FROM sys.stats\nWHERE object_id= OBJECT_ID('Person.Person');<\/pre>\n
 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<\/code><\/b>  catalog view. The  SELECT<\/code><\/b> statement  returns the following results:<\/p>\n\n\n\n\n\n\n\n\n
\n
 \t\tname<\/b><\/p>\n<\/td>\n
\n
 \t\tstats_id<\/b><\/p>\n<\/td>\n
\n
 \t\tStatsDate<\/b><\/p>\n<\/td>\n<\/tr>\n
\n
 \t\tPK_Person_BusinessEntityID<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n
\n
2014-07-17  \t\t16:11:30.067<\/p>\n<\/td>\n<\/tr>\n
\n
 \t\tIX_Person_LastName_FirstName_MiddleName<\/p>\n<\/td>\n
\n
2<\/p>\n<\/td>\n
\n
2014-07-17  \t\t16:11:31.783<\/p>\n<\/td>\n<\/tr>\n
\n
AK_Person_rowguid<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n
\n
2014-07-17  \t\t16:11:31.807<\/p>\n<\/td>\n<\/tr>\n
\n
 \t\t_WA_Sys_00000004_693CA210<\/p>\n<\/td>\n
\n
4<\/p>\n<\/td>\n
\n
2015-06-05  \t\t11:37:38.303<\/p>\n<\/td>\n<\/tr>\n
\n
 \t\t_WA_Sys_00000002_693CA210<\/p>\n<\/td>\n
\n
5<\/p>\n<\/td>\n
\n
2015-08-04  \t\t17:31:51.280<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
If you’re  using sequence objects in your database, you can use the NEXT VALUE FOR<\/code><\/b>  metadata function to generate the next number for the specified sequence:<\/p>\n
SELECT NEXT VALUEFOR AdventureWorks2014.dbo.AWSequence;<\/pre>\n
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<\/code><\/b>, the second time, the value 102<\/code><\/b>, and  so on.<\/p>\n
Now let’s  look at one more metadata function:  SCOPE_IDENTITY<\/code><\/b>.  The function returns that last value inserted into an IDENTITY<\/code><\/b>  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<\/code><\/b>  table, then creates the  InsertProducts<\/code><\/b>  stored procedure, which includes the  SCOPE_IDENTITY<\/code><\/b>  function, and finally runs an  EXEC<\/code><\/b> statement  against the stored procedure:<\/p>\n
IF OBJECT_ID (N'dbo.Products', N'U') IS NOT NULL\nDROP TABLE dbo.Products;\nGO\n \nCREATE TABLE dbo.Products\n(ProductID INT PRIMARY KEY IDENTITY(101, 1),\nProductName NVARCHAR(50) NOT NULL);\nGO\n \nIF OBJECT_ID (N'dbo.InsertProduct', N'P') IS NOT NULL\nDROP PROCEDURE dbo.InsertProduct;\nGO\n \nCREATE PROCEDURE dbo.InsertProduct\n  (@ProdName VARCHAR(50) = NULL)\nAS\nIF @ProdName IS NOT NULL\nBEGIN\n  INSERT INTO dbo.Products(ProductName)\n  VALUES(@ProdName);\n  SELECT SCOPE_IDENTITY();\nEND\n \nEXEC InsertProduct 'widget2';\n\t<\/pre>\n
After the  stored procedure’s  INSERT<\/code><\/b> statement  runs, a  SELECT<\/code><\/b> statement calls the SCOPE_IDENTITY<\/code><\/b>  function. When we run the  EXEC<\/code><\/b> statement for  the first time, the  SELECT<\/code><\/b> statement returns a value of  101<\/code><\/b> (our first  IDENTITY<\/code><\/b> value)  and continues from there, incrementing by one each time we add a row. Returning  the  IDENTITY<\/code><\/b> 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.<\/p>\n
SQL Server metadata functions<\/h1>\n
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. <\/p>\n
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.<\/p>\n<\/p><\/div>\n","protected":false},"excerpt":{"rendered":"
To be able to make full use of the system catalog to find out more about a database, you need to be familiar with the metadata functions. They save a great deal of time and typing when querying the metadata. Once you get the hang of these functions, the system catalog suddenly seems simple to use, as Robert Sheldon demonstrates in this article.…<\/p>\n","protected":false},"author":221841,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143531],"tags":[4242,4168,4150,4151,4252],"coauthors":[],"class_list":["post-2184","post","type-post","status-publish","format-standard","hentry","category-t-sql-programming-sql-server","tag-basics","tag-database","tag-sql","tag-sql-server","tag-t-sql-programming"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2184","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221841"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=2184"}],"version-history":[{"count":5,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2184\/revisions"}],"predecessor-version":[{"id":41286,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2184\/revisions\/41286"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=2184"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=2184"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=2184"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=2184"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}