Metadata Functions in SQL Server Part 1

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

• Mathematical Function
• String Function
• Aggregate Function
• Date And Time Function

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:





First we create a table.

GO

CREATE TABLE [dbo].[Employee](
[Emp_ID] [int] NOT NULL,
[Emp_Name] [nvarchar](50) NOT NULL,
[Emp_Salary] [int] NOT NULL,
[Emp_City] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
[Emp_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Now insert some values into the table.

Insert into Employee
Select 1,'Pankaj',25000,'Alwar' Union All
Select 2,'Rahul',26000,'Jaipur' Union All
Select 3,'Sandeep',25000,'Alwar' Union All
Select 4,'Sanjeev',24000, 'Jaipur' Union All
Select 5,'Neeraj',28000,'Alwar' Union All
Select 6,'Naru',20000, 'Jaipur' Union All
Select 7,'Omi',23000,'Alwar'

Select all the values from the 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



Example

SELECT 'Emp_ID' AS COLUMN_NAME, COL_LENGTH('Employee', 'Emp_ID') as [LENGTH] UNION ALL
SELECT 'Emp_Name', COL_LENGTH('Employee', 'Emp_Name') UNION ALL
SELECT 'Emp_Salary',COL_LENGTH('Employee', 'Emp_Salary') UNION ALL
SELECT 'Emp_City' ,COL_LENGTH('Employee', 'Emp_City') UNION ALL
SELECT 'Emp_State' ,COL_LENGTH('Employee', 'Emp_State')

Output



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


Example

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

Output



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

Example

SELECT COLUMNPROPERTY(OBJECT_ID('Employee'),'Emp_ID', 'AllowsNull') AS [PROPERTY] UNION ALL
SELECT COLUMNPROPERTY(OBJECT_ID('Employee'),'Emp_ID', 'IsIdentity') UNION ALL
SELECT COLUMNPROPERTY(OBJECT_ID('Employee'), 'Emp_City', 'AllowsNull')

Output



Example

Alter Table Employee
Alter Column Emp_City nvarchar(50) Null


SELECT COLUMNPROPERTY(OBJECT_ID('Employee'),'Emp_ID', 'AllowsNull') AS [PROPERTY] UNION ALL
SELECT COLUMNPROPERTY(OBJECT_ID('Employee'),'Emp_ID', 'IsIdentity') UNION ALL
SELECT COLUMNPROPERTY(OBJECT_ID('Employee'),'Emp_City', 'AllowsNull')

Output



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







Example

SELECT DATABASEPROPERTY('master', 'IsTruncLog') AS [Result] UNION ALL
SELECT DATABASEPROPERTY('practice', 'IsTruncLog') UNION ALL
SELECT DATABASEPROPERTY('practice', 'Version') UNION ALL
SELECT DATABASEPROPERTY('master', 'Version') UNION ALL
SELECT DATABASEPROPERTY('master', 'IsTruncLog') UNION ALL
SELECT DATABASEPROPERTY('practice', 'IsTruncLog')

Output



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









Example

SELECT DATABASEPROPERTYEX('master', 'IsAutoShrink') AS [Result] UNION ALL
SELECT DATABASEPROPERTYEX('practice', 'IsAutoShrink') UNION ALL
SELECT DATABASEPROPERTYEX('practice', 'UserAccess') UNION ALL
SELECT DATABASEPROPERTYEX('master', 'UserAccess') UNION ALL
SELECT DATABASEPROPERTYEX('master', 'Updateability') UNION ALL
SELECT DATABASEPROPERTYEX('practice', 'Updateability')

Output



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



Example

SELECT DB_ID('master') ID, 'MASTER' AS NAME UNION ALL
SELECT DB_ID('model') ID, 'MODEL' AS NAME UNION ALL
SELECT DB_ID('tempdb') ID, 'TEMPDB' AS NAME UNION ALL
SELECT DB_ID('msdb') ID, 'MSDB' AS NAME UNION ALL
SELECT DB_ID('practice') ID, 'PRACTICE' AS NAME

Output



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)



Example

SELECT DB_ID('master') ID, DB_NAME(DB_ID('master')) AS NAME UNION ALL
SELECT DB_ID('model') ID, DB_NAME(DB_ID('model')) AS NAME UNION ALL
SELECT DB_ID('tempdb') ID, DB_NAME(DB_ID('tempdb')) AS NAME UNION ALL
SELECT DB_ID('msdb') ID, DB_NAME(DB_ID('msdb')) AS NAME UNION ALL
SELECT DB_ID('practice') ID, DB_NAME(DB_ID('practice')) AS NAME

Output



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



Example

USE MASTER
SELECT 'MASTER' AS [NAME] , FILE_ID('MASTER') AS [FILE_ID]
USE PRACTICE
SELECT 'PRACTICE' AS [NAME] , FILE_ID('PRACTICE') AS [FILE_ID]

Output



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)



Example

USE MASTER
SELECT '1' AS [ID] , FILE_NAME(1) AS [FILE_NAME] UNION ALL
SELECT '2' AS [ID] , FILE_NAME(2) AS [FILE_NAME]
USE PRACTICE
SELECT '1' AS [ID] , FILE_NAME(1) AS [FILE_NAME] UNION ALL
SELECT '2' AS [ID] , FILE_NAME(2) AS [FILE_NAME]

Output



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



Example

USE MASTER
SELECT 'MASTER' AS [ID] , FILE_IDEX('MASTER') AS [FILE_ID] UNION ALL
SELECT 'MASLOG' AS [ID] , FILE_IDEX('MASTLOG') AS [FILE_ID]
USE PRACTICE
SELECT 'PRACTICE' AS [ID] , FILE_IDEX('PRACTICE') AS [FILE_ID] UNION ALL
SELECT 'PRACTICE_LOG' AS [ID] , FILE_IDEX('PRACTICE_LOG') AS [FILE_ID]

Output



Example

USE tempdb;
GO
SELECT FILE_IDEX((SELECT TOP(1)name FROM sys.database_files
WHERE type = 1))AS 'File ID';
GO

Output



Example

USE tempdb;
GO
SELECT FILE_IDEX((SELECT TOP(1)name FROM sys.database_files
WHERE type = 1))AS 'File ID' UNION ALL
SELECT FILE_IDEX((SELECT name FROM sys.master_files WHERE type = 4))
AS 'File_ID';
GO

Output



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



Example

DECLARE @NAME [nvarchar](MAX);
SET @NAME=(SELECT [name] FROM sys.filegroups)

SELECT FILEGROUP_ID(@NAME) AS [Filegroup ID];
GO

Output



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)



Example

DECLARE @ID INT;
SET @ID=(SELECT f.data_space_id FROM sys.filegroups f)
PRINT @ID
SELECT FILEGROUP_NAME(@ID) AS [Filegroup NAME];
GO

Output



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



Example

DECLARE @NAME [nvarchar](MAX);
SET @NAME=(SELECT f.name FROM sys.filegroups f)

SELECT FILEGROUPPROPERTY(@NAME,'IsDefault') AS [Value],'Default Filegroup' AS [Filegroup Property] UNION ALL
SELECT FILEGROUPPROPERTY(@NAME,'IsReadOnly'), 'IsReadOnly' UNION ALL
SELECT FILEGROUPPROPERTY(@NAME,'IsUserDefinedFG') ,'IsUserDefinedFG'
GO

Output



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



Example

USE master
SELECT FILEPROPERTY('master', 'IsPrimaryFile')AS [Primary File] UNION ALL
SELECT FILEPROPERTY('master', 'IsReadOnly')AS [Primary File] UNION ALL
SELECT FILEPROPERTY('master', 'IsLogFile')AS [Primary File] UNION ALL
SELECT FILEPROPERTY('master', 'SpaceUsed')AS [Primary File]
GO

Output



Example

USE PRACTICE
SELECT FILEPROPERTY('PRACTICE_LOG', 'IsPrimaryFile')AS [Primary File] UNION ALL
SELECT FILEPROPERTY('PRACTICE_LOG', 'IsReadOnly')AS [Primary File] UNION ALL
SELECT FILEPROPERTY('PRACTICE_LOG', 'IsLogFile')AS [Primary File] UNION ALL
SELECT FILEPROPERTY('PRACTICE_LOG', 'SpaceUsed')AS [Primary File]
GO

Output



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



Example

SELECT TYPE_ID('INT') [TYPE_ID] , 'INT' [DATATYPE] UNION ALL
SELECT TYPE_ID('bigint') [TYPE_ID] , 'bigint' [DATATYPE] UNION ALL
SELECT TYPE_ID('binary') [TYPE_ID] , 'binary' [DATATYPE] UNION ALL
SELECT TYPE_ID('bit') [TYPE_ID] , 'bit' [DATATYPE] UNION ALL
SELECT TYPE_ID('char') [TYPE_ID] , 'char' [DATATYPE] UNION ALL
SELECT TYPE_ID('date') [TYPE_ID] , 'date' [DATATYPE] UNION ALL
SELECT TYPE_ID('datetime') [TYPE_ID] , 'datetime' [DATATYPE] UNION ALL
SELECT TYPE_ID('datetime2') [TYPE_ID] , 'datetime2' [DATATYPE] UNION ALL
SELECT TYPE_ID('float') [TYPE_ID] , 'float' [DATATYPE] UNION ALL
SELECT TYPE_ID('money') [TYPE_ID] , 'money' [DATATYPE] UNION ALL
SELECT TYPE_ID('text') [TYPE_ID] , 'text' [DATATYPE]

Output



Example 

CREATE TYPE NewData_Type FROM int;
GO
CREATE SCHEMA NewSchema;
GO
CREATE TYPE NewSchema.NewData_Type FROM int;
GO
SELECT TYPE_ID('NewData_Type') AS [1 Part Data Type ID],
TYPE_ID('NewSchema.NewData_Type') AS [2 Part Data Type ID];
GO

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.