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:
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')
OutputNote
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)
OutputCOLUMPROPERTY
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')
OutputExample
- 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')
OutputDATABASEPROPERTY
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')
OutputDATABASEPROPERTYEX
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')
OutputDB_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
OutputDB_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
OutputFILE_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]
OutputFILE_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]
OutputExample
- 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
OutputFILEGROUP_NAME
The FILEGROUP_NAME function returns the filegroup name for the specified filegroup identification (ID) number. filegroup_id corresponds to the d
ata_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
OutputFILEGROUPPROPERTY
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
OutputFILEPROPERTY
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
OutputExample
- 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
OutputTYPE_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]
OutputExample
- 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.