SQL Tips - Meta Data User-Defined Functions

Introduction

Meta Data UDFs

COL_LENGTH2

COL_ID

INDEX_ID

INDEX_COL2

ROW_COUNT

---

Introduction

I would like to write the series of articles about useful User-Defined Functions grouped by the following categories:

Date and Time User-Defined Functions

Mathematical User-Defined Functions

Metadata User-Defined Functions

Security User-Defined Functions

String User-Defined Functions

System User-Defined Functions

Text and Image User-Defined Functions

In this article, I wrote some useful Meta Data User-Defined Functions.

Meta Data UDFs

These scalar User-Defined Functions return information about the database and database objects.

COL_LENGTH2

Returns the defined length (in bytes) of a column for a given table and for a given database.

Syntax

COL_LENGTH2 ( 'database' , 'table' , 'column' )

Arguments

'database' - the name of the database. database is an expression of type nvarchar.
'table' - the name of the table for which to determine column length information. table is an expression of type nvarchar.
'column' - the name of the column for which to determine length. column is an expression of type nvarchar.

Return Types

int

The function's text:

CREATE FUNCTION COL_LENGTH2 ( @database sysname, @table sysname, @column sysname ) RETURNS int AS BEGIN RETURN (COL_LENGTH(@database + '..' + @table, @column)) END GO

Examples

This example returns the defined length (in bytes) of the au_id column of the authors table in the pubs database:

SELECT dbo.COL_LENGTH2('pubs', 'authors', 'au_id')

Here is the result set:

-----------
11

(1 row(s) affected)

COL_ID

Returns the ID of a database column given the corresponding table name and column name.

Syntax

COL_ID ( 'table' , 'column' )

Arguments

'table' - the name of the table. table is an expression of type nvarchar.
'column' - the name of the column. column is an expression of type nvarchar.

Return Types

int

The function's text:

CREATE FUNCTION COL_ID ( @table sysname, @column sysname ) RETURNS int AS BEGIN DECLARE @col_id int SELECT @col_id = colid FROM syscolumns WHERE id = OBJECT_ID(@table) AND name = @column RETURN @col_id END GO

Examples

This example returns the ID of the au_fname column of the authors table in the pubs database:

USE pubs GO SELECT dbo.COL_ID('authors', 'au_fname') GO

Here is the result set:

-----------
3

(1 row(s) affected)

INDEX_ID

Returns the ID of an index given the corresponding table name and index name.

Syntax

INDEX_ID ( 'table' , 'index_name' )

Arguments

'table' - the name of the table. table is an expression of type nvarchar.
'index_name' - the name of the index. index_name is an expression of type nvarchar.

Return Types

int

The function's text:

CREATE FUNCTION INDEX_ID ( @table sysname, @index_name sysname ) RETURNS int AS BEGIN DECLARE @indid int SELECT @indid = indid FROM sysindexes WHERE id = OBJECT_ID(@table) AND name = @index_name RETURN @indid END GO

Examples

This example returns the ID of the aunmind index of the authors table in the pubs database:

USE pubs GO SELECT dbo.INDEX_ID('authors', 'aunmind') GO

Here is the result set:

------
2

(1 row(s) affected)

INDEX_COL2

Returns the indexed column name for a given table and for a given database.

Syntax

INDEX_COL2 ( 'database' , 'table' , index_id , key_id )

Arguments

'database' - the name of the database. database is an expression of type nvarchar.
'table' - the name of the table.
index_id - the ID of the index.
key_id - the ID of the key.

Return Types

nvarchar (256)

The function's text:

CREATE FUNCTION INDEX_COL2 ( @database sysname, @table sysname, @index_id int, @key_id int ) RETURNS int AS BEGIN RETURN (INDEX_COL(@database + '..' + @table, @index_id, @key_id)) END GO

Examples

This example returns the indexed column name of the authors table in the pubs database (for index_id = 2 and key_id = 1):

SELECT dbo.INDEX_COL2('pubs', 'authors', 2, 1)

Here is the result set:

-----------------------
au_lname

(1 row(s) affected)

ROW_COUNT

Returns the total row count for a given table.

Syntax

ROW_COUNT ( 'table' )

Arguments

'table' - the name of the table for which to determine the total row count. table is an expression of type nvarchar.

Return Types

int

The function's text:

CREATE FUNCTION ROW_COUNT ( @table sysname ) RETURNS int AS BEGIN DECLARE @row_count int SELECT @row_count = rows FROM sysindexes WHERE id = OBJECT_ID(@table) AND indid < 2 RETURN @row_count END GO

Examples

This example returns the total row count of the authors table in the pubs database:

USE pubs GO SELECT dbo.ROW_COUNT('authors') GO

Here is the result set:

-----------
23

(1 row(s) affected)

See this link for more information:
Alternative way to get the table's row count