Metadata Functions in SQL Server Part 1

Hello friends. This is my fifth article of System Functions. My last 4 articles are:

Today we read about Metadata Functions in SQL Server.

Metadata functions return information about database objects. If you're familiar with the system tables and the INFORMATION_SCHEMA views, you can get most of the information available from metadata functions yourself. All metadata functions are non-deterministic. This means these functions do not always return the same results every time they are called, even with the same set of input values.
SQL Server contain the following Metadata functions:

Meta Data Functions

Meta Data

First we create a table.

  1. GO  
  2.   
  3. CREATE TABLE [dbo].[Employee](  
  4. [Emp_ID] [intNOT NULL,  
  5. [Emp_Name] [nvarchar](50) NOT NULL,  
  6. [Emp_Salary] [intNOT NULL,  
  7. [Emp_City] [nvarchar](50) NOT NULL,  
  8. CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED   
  9. (  
  10. [Emp_ID] ASC  
  11. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY]  
  12. ON [PRIMARY]  
  13.   
  14. GO  
Now insert some values into the table.
  1. Insert into Employee  
  2. Select 1,'Pankaj',25000,'Alwar' Union All  
  3. Select 2,'Rahul',26000,'Jaipur' Union All  
  4. Select 3,'Sandeep',25000,'Alwar' Union All  
  5. Select 4,'Sanjeev',24000, 'Jaipur' Union All  
  6. Select 5,'Neeraj',28000,'Alwar' Union All  
  7. Select 6,'Naru',20000, 'Jaipur' Union All  
  8. Select 7,'Omi',23000,'Alwar'   
Select all the values from the table.

Select all value from table

Now we will see all the Metadata functions.

COL_LENGTH

The COL_LENGTH function returns the defined length, in bytes, of a column. It returns NULL on error or if a caller does not have permission to view the object. In SQL Server, a user can only view the metadata of securables that the user owns or on which the user has been granted permission.

Syntax

COL_LENGTH ( 'table' , 'column' )

table:
Is the name of the table to determine column length information for. It is an expression of type nvarchar.
column: Is the name of the column to the determine length for. It is an expression of type nvarchar.

Return Type:
int

COL LENGTH

Example
  1. SELECT 'Emp_ID' AS COLUMN_NAME, COL_LENGTH('Employee''Emp_ID'as [LENGTH] UNION ALL  
  2. SELECT 'Emp_Name', COL_LENGTH('Employee''Emp_Name'UNION ALL  
  3. SELECT 'Emp_Salary',COL_LENGTH('Employee''Emp_Salary'UNION ALL  
  4. SELECT 'Emp_City' ,COL_LENGTH('Employee''Emp_City'UNION ALL  
  5. SELECT 'Emp_State' ,COL_LENGTH('Employee''Emp_State')  
Output

Emp State

Note

Here Emp_State is NULL because the table doesn't contain any field for Emp_State. The length of Emp_Name and Emp_City field is just double as we define because an nvarchar field occuies double the space in memory as we define.

COL_NAME

The COL_NAME function returns the name of a column from a specified corresponding table identification number and column identification number. It returns NULL on error or if a caller does not have permission to view the object. A user can only view the metadata of securables that the user owns or on which the user has been granted permission. The table_id and column_id parameters together produce a column name string.

Syntax

COL_NAME ( table_id , column_id )

table_id: Is the identification number of the table that contains the column. table_id is of type int.
column_id: Is the identification number of the column. The column_id parameter is of type int.

Return type: sysname

sysname
Example
  1. SELECT 'Emp_ID' AS COLUMN_NAME, COL_NAME(OBJECT_ID('Employee'), 1) AS COLUMN_NAME UNION ALL  
  2. SELECT 'Emp_Name', COL_NAME(OBJECT_ID('Employee'), 2) UNION ALL  
  3. SELECT 'Emp_Salary',COL_NAME(OBJECT_ID('Employee'), 3) UNION ALL  
  4. SELECT 'Emp_City' ,COL_NAME(OBJECT_ID('Employee'), 4)UNION ALL  
  5. SELECT 'Emp_State' ,COL_NAME(OBJECT_ID('Employee'), 5)  
Output

UNION ALL

COLUMPROPERTY

The COLUMNPROPERTY function returns information about a column or parameter. It returns NULL on error or if a caller does not have permission to view the object. A user can only view the metadata of securables that the user owns or on which the user has been granted permission. When you check the deterministic property of a column, first test whether the column is a computed column. IsDeterministic returns NULL for noncomputed columns. Computed columns can be specified as index columns.

Syntax

COLUMNPROPERTY ( id , column , property )

Id: Is an expression that contains the identifier (ID) of the table or procedure.
Column: Is an expression that contains the name of the column or parameter.
Property: Is an expression that contains the information to be returned for id, and can be any one of the following values.
 
Return Type: int

COLUMNPROPERTY

isidentity

isprecise

Example
  1. SELECT COLUMNPROPERTY(OBJECT_ID('Employee'),'Emp_ID''AllowsNull'AS [PROPERTY] UNION ALL  
  2. SELECT COLUMNPROPERTY(OBJECT_ID('Employee'),'Emp_ID''IsIdentity'UNION ALL  
  3. SELECT COLUMNPROPERTY(OBJECT_ID('Employee'), 'Emp_City''AllowsNull')  
Output

Table Employee

Example
  1. Alter Table Employee  
  2. Alter Column Emp_City nvarchar(50) Null  
  3.   
  4.   
  5. SELECT COLUMNPROPERTY(OBJECT_ID('Employee'),'Emp_ID''AllowsNull'AS [PROPERTY] UNION ALL  
  6. SELECT COLUMNPROPERTY(OBJECT_ID('Employee'),'Emp_ID''IsIdentity'UNION ALL  
  7. SELECT COLUMNPROPERTY(OBJECT_ID('Employee'),'Emp_City''AllowsNull')  
Output

Employee

DATABASEPROPERTY

The DATABASEPROPERTY function returns the named database property value for the given database and property name. The value returned is also NULL if the database has never been started, or has been auto-closed.

Syntax

DATABASEPROPERTY( database , property )

Database: Is an expression containing the name of the database to return the named property information for. It is an nvarchar(128).

Property: Is an expression containing the name of the database property to return. property is varchar(128), and can be one of these values.

Return Type: int

database

value return

version

Example
  1. SELECT DATABASEPROPERTY('master''IsTruncLog'AS [Result] UNION ALL  
  2. SELECT DATABASEPROPERTY('practice''IsTruncLog'UNION ALL  
  3. SELECT DATABASEPROPERTY('practice''Version'UNION ALL  
  4. SELECT DATABASEPROPERTY('master''Version'UNION ALL  
  5. SELECT DATABASEPROPERTY('master''IsTruncLog'UNION ALL  
  6. SELECT DATABASEPROPERTY('practice''IsTruncLog')   
Output

IsTruncLog

DATABASEPROPERTYEX

The DATABASEPROPERTYEX function returns the current setting of the specified database option or property for the specified database in SQL Server. It returns NULL on error or if a caller does not have permission to view the object. In SQL Server, a user can only view the metadata of securables that the user owns or on which the user has been granted permission.

Syntax

DATABASEPROPERTYEX ( database , property )

Database: Is an expression that represents the name of the database to return the named property information for. database is nvarchar(128). For a SQL database, it must be the name of the current database. It returns NULL for all properties if a different database name is provided.

Property: Is an expression that represents the name of the database property to return. property is varchar(128), and can be one of the following values. The return type is sql_variant. The following table shows the base data type for each property value.

Return Type: sql_variant

DATABASEPROPERTYEX

value

recovery

status

Example
  1. SELECT DATABASEPROPERTYEX('master''IsAutoShrink'AS [Result] UNION ALL  
  2. SELECT DATABASEPROPERTYEX('practice''IsAutoShrink'UNION ALL  
  3. SELECT DATABASEPROPERTYEX('practice''UserAccess'UNION ALL  
  4. SELECT DATABASEPROPERTYEX('master''UserAccess'UNION ALL  
  5. SELECT DATABASEPROPERTYEX('master''Updateability'UNION ALL  
  6. SELECT DATABASEPROPERTYEX('practice''Updateability')   
Output

multi user

DB_ID

The DB_ID function returns the database identification (ID) number.

Syntax

DB_ID ( [ 'database_name' ] )

Database_name: Is the database name used to return the corresponding database ID. database_name is sysname. If database_name is omitted, the current database ID is returned.

Return Type: int

Database name

Example
  1. SELECT DB_ID('master') ID, 'MASTER' AS NAME UNION ALL  
  2. SELECT DB_ID('model') ID, 'MODEL' AS NAME UNION ALL  
  3. SELECT DB_ID('tempdb') ID, 'TEMPDB' AS NAME UNION ALL  
  4. SELECT DB_ID('msdb') ID, 'MSDB' AS NAME UNION ALL  
  5. SELECT DB_ID('practice') ID, 'PRACTICE' AS NAME   
Output

AS NAME

DB_NAME

The DB_NAME function returns the database name. If the caller of DB_NAME is not the owner of the database and the database is not master or tempdb, the minimum permissions required to see the corresponding row are ALTER ANY DATABASE or VIEW ANY DATABASE server-level permission, or CREATE DATABASE permission in the master database.

Syntax

DB_NAME ( [ database_id ] )

database_id: Is the identification number (ID) of the database to be returned. database_id is an int, with no default. If no ID is specified, the current database name is returned.

Return Type: nvarchar(128)

DB NAME

Example
  1. SELECT DB_ID('master') ID, DB_NAME(DB_ID('master')) AS NAME UNION ALL  
  2. SELECT DB_ID('model') ID, DB_NAME(DB_ID('model')) AS NAME UNION ALL  
  3. SELECT DB_ID('tempdb') ID, DB_NAME(DB_ID('tempdb')) AS NAME UNION ALL  
  4. SELECT DB_ID('msdb') ID, DB_NAME(DB_ID('msdb')) AS NAME UNION ALL  
  5. SELECT DB_ID('practice') ID, DB_NAME(DB_ID('practice')) AS NAME   
Output

SELECT DB

FILE_ID

The FILE_ID function returns the file identification (ID) number for the given logical file name in the current database. file_name corresponds to the logical file name displayed in the name column in the sys.master_files or sys.database_files catalog views.

Syntax:

FILE_ID ( file_name )

file_name: Is an expression of type sysname that represents the name of the file for which to return the file ID.

Return Type: smallint

code

Example
  1. USE MASTER  
  2. SELECT 'MASTER' AS [NAME] , FILE_ID('MASTER'AS [FILE_ID]   
  3. USE PRACTICE  
  4. SELECT 'PRACTICE' AS [NAME] , FILE_ID('PRACTICE'AS [FILE_ID]   
Output

SELECT

FILE_NAME

The FILE_NAME function returns the logical file name for the given file identification (ID) number. file_ID corresponds to the file_id column in the sys.master_files or sys.database_files catalog views.

Syntax

FILE_NAME ( file_id )


file_id: Is the file identification number for which to return the file name. file_id is an int.

Return type: nvarchar(128)

file name

Example

  1. USE MASTER  
  2. SELECT '1' AS [ID] , FILE_NAME(1) AS [FILE_NAME] UNION ALL  
  3. SELECT '2' AS [ID] , FILE_NAME(2) AS [FILE_NAME]  
  4. USE PRACTICE  
  5. SELECT '1' AS [ID] , FILE_NAME(1) AS [FILE_NAME] UNION ALL  
  6. SELECT '2' AS [ID] , FILE_NAME(2) AS [FILE_NAME]  
Output

UNION

FILE_IDEX

The FILE_IDEX function returns the file identification (ID) number for the specified logical file name of the data, log, or full-text file in the current database. FILE_IDEX can be used in a select list, a WHERE clause, or anywhere an expression is allowed. file_name corresponds to the logical file name displayed in the name column in the sys.master_files or sys.database_files catalog views.

Syntax

FILE_IDEX ( file_name )

file_name: Is an expression of type sysname that represents the name of the file for which to return the file ID.

Return Type: int NULL on error

FILE IDEX

Example
  1. USE MASTER  
  2. SELECT 'MASTER' AS [ID] , FILE_IDEX('MASTER'AS [FILE_ID] UNION ALL  
  3. SELECT 'MASLOG' AS [ID] , FILE_IDEX('MASTLOG'AS [FILE_ID]  
  4. USE PRACTICE  
  5. SELECT 'PRACTICE' AS [ID] , FILE_IDEX('PRACTICE'AS [FILE_ID] UNION ALL  
  6. SELECT 'PRACTICE_LOG' AS [ID] , FILE_IDEX('PRACTICE_LOG'AS [FILE_ID]  
Output

PRACTICE

Example
  1. USE tempdb;  
  2. GO  
  3. SELECT FILE_IDEX((SELECT TOP(1)name FROM sys.database_files   
  4. WHERE type = 1))AS 'File ID';  
  5. GO  
Output

file


Example
  1. USE tempdb;  
  2. GO  
  3. SELECT FILE_IDEX((SELECT TOP(1)name FROM sys.database_files   
  4. WHERE type = 1))AS 'File ID' UNION ALL  
  5. SELECT FILE_IDEX((SELECT name FROM sys.master_files WHERE type = 4))  
  6. AS 'File_ID';  
  7. GO  
Output

file id


In this example the first query is similar to the previous example. The second query returns NULL because this query returns the file ID of a full-text file by selecting the logical file name from the sys.database_files catalog view where the file type is equal to 4 (full-text).

FILEGROUP_ID

The FILEGROUP_ID function returns the filegroup identification (ID) number for a specified filegroup name. filegroup_name corresponds to the name column in the sys.filegroups catalog view.

Syntax

FILEGROUP_ID ( 'filegroup_name' )

filegroup_name: Is an expression of type sysname that represents the filegroup name for which to return the filegroup ID.

Return Type: int

FILEGROUP ID

Example
  1. DECLARE @NAME [nvarchar](MAX);  
  2. SET @NAME=(SELECT [nameFROM sys.filegroups)  
  3.   
  4. SELECT FILEGROUP_ID(@NAMEAS [Filegroup ID];  
  5. GO  
Output

Filegroup

FILEGROUP_NAME

The FILEGROUP_NAME function returns the filegroup name for the specified filegroup identification (ID) number. filegroup_id corresponds to the data_space_id column in the sys.filegroups catalog view.

Syntax

FILEGROUP_NAME ( filegroup_id )

filegroup_id: Is the filegroup ID number for which to return the filegroup name. filegroup_id is a smallint.

Return Type: nvarchar(128)

FILEGROUP NAME

Example
  1. DECLARE @ID INT;  
  2. SET @ID=(SELECT f.data_space_id FROM sys.filegroups f)  
  3. PRINT @ID  
  4. SELECT FILEGROUP_NAME(@ID) AS [Filegroup NAME];  
  5. GO  
Output

NAME

FILEGROUPPROPERTY

The FILEGROUPPROPERTY function returns the specified filegroup property value when supplied with a filegroup and property name. filegroup_name corresponds to the name column in the sys.filegroups catalog view.

Syntax

FILEGROUPPROPERTY ( filegroup_name , property )

filegroup_name: Is an expression of type sysname that represents the name of the filegroup for which to return the named property information.

Property: Is an expression of type varchar(128) that contains the name of the filegroup property to return. property can be one of these values.

Return Type: int

FILEGROUPPROPERTY

Example
  1. DECLARE @NAME [nvarchar](MAX);  
  2. SET @NAME=(SELECT f.name FROM sys.filegroups f)  
  3.   
  4. SELECT FILEGROUPPROPERTY(@NAME,'IsDefault'AS [Value],'Default Filegroup' AS [Filegroup Property] UNION ALL  
  5. SELECT FILEGROUPPROPERTY(@NAME,'IsReadOnly'), 'IsReadOnly' UNION ALL  
  6. SELECT FILEGROUPPROPERTY(@NAME,'IsUserDefinedFG') ,'IsUserDefinedFG'   
  7. GO  
Output

view

FILEPROPERTY

The FILEPROPERTY function returns the specified file name property value when a file name in the current database and a property name are specified. It returns NULL for files that are not in the current database. file_name corresponds to the name column in the sys.master_files or sys.database_files catalog view.

Syntax

FILEPROPERTY ( file_name , property )

file_name: Is an expression that contains the name of the file associated with the current database for which to return property information. file_name is nchar(128).

property: Is an expression that contains the name of the file property to return. property is varchar(128), and can be one of the following values.

Return Type: int

FILEPROPERTY

Example
  1. USE master  
  2. SELECT FILEPROPERTY('master''IsPrimaryFile')AS [Primary File] UNION ALL  
  3. SELECT FILEPROPERTY('master''IsReadOnly')AS [Primary File] UNION ALL  
  4. SELECT FILEPROPERTY('master''IsLogFile')AS [Primary File] UNION ALL  
  5. SELECT FILEPROPERTY('master''SpaceUsed')AS [Primary File]   
  6. GO  
Output

primary file

Example
  1. USE PRACTICE  
  2. SELECT FILEPROPERTY('PRACTICE_LOG''IsPrimaryFile')AS [Primary File] UNION ALL  
  3. SELECT FILEPROPERTY('PRACTICE_LOG''IsReadOnly')AS [Primary File] UNION ALL  
  4. SELECT FILEPROPERTY('PRACTICE_LOG''IsLogFile')AS [Primary File] UNION ALL  
  5. SELECT FILEPROPERTY('PRACTICE_LOG''SpaceUsed')AS [Primary File]   
  6. GO  
Output

run

TYPE_ID

The TYPE_ID function returns the ID for a specified data type name. It returns NULL on error or if a caller does not have permission to view the object. TYPE_ID returns NULL if the type name is not valid, or if the caller does not have sufficient permission to reference the type. In SQL Server, a user can only view the metadata of securables that the user owns or on which the user has been granted permission. This means that metadata-emitting and built-in functions such as TYPE_ID may return NULL if the user does not have any permission on the object.

Syntax

TYPE_ID ( [ schema_name ] type_name )

type_name: Is the name of the data type. type_name is of type nvarchar. type_name can be a system or user-defined data type.

Return Type: int

TYPE ID

Example
  1. SELECT TYPE_ID('INT') [TYPE_ID] , 'INT' [DATATYPE] UNION ALL  
  2. SELECT TYPE_ID('bigint') [TYPE_ID] , 'bigint' [DATATYPE] UNION ALL  
  3. SELECT TYPE_ID('binary') [TYPE_ID] , 'binary' [DATATYPE] UNION ALL  
  4. SELECT TYPE_ID('bit') [TYPE_ID] , 'bit' [DATATYPE] UNION ALL  
  5. SELECT TYPE_ID('char') [TYPE_ID] , 'char' [DATATYPE] UNION ALL  
  6. SELECT TYPE_ID('date') [TYPE_ID] , 'date' [DATATYPE] UNION ALL  
  7. SELECT TYPE_ID('datetime') [TYPE_ID] , 'datetime' [DATATYPE] UNION ALL  
  8. SELECT TYPE_ID('datetime2') [TYPE_ID] , 'datetime2' [DATATYPE] UNION ALL  
  9. SELECT TYPE_ID('float') [TYPE_ID] , 'float' [DATATYPE] UNION ALL  
  10. SELECT TYPE_ID('money') [TYPE_ID] , 'money' [DATATYPE] UNION ALL  
  11. SELECT TYPE_ID('text') [TYPE_ID] , 'text' [DATATYPE]   
Output

result

Example 
  1. CREATE TYPE NewData_Type FROM int;  
  2. GO  
  3. CREATE SCHEMA NewSchema;  
  4. GO  
  5. CREATE TYPE NewSchema.NewData_Type FROM int;  
  6. GO  
  7. SELECT TYPE_ID('NewData_Type'AS [1 Part Data Type ID],  
  8. TYPE_ID('NewSchema.NewData_Type'AS [2 Part Data Type ID];  
  9. GO  
Output

Output

Explanation of all Meta Function are not possible in a single article. So I will explain all the remaining Meta functions in my next article.


Similar Articles