• 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.

To download Meta Data User-Defined Functions click this link:

Download

Meta Data UDFs

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' - Is the name of the database. database is an expression of type nvarchar.

'table' - Is the name of the table for which to determine column length

information. table is an expression of type nvarchar.

'column' - Is 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')

GO

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' - Is the name of the table. table is an expression of type nvarchar.

'column' - Is 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' - Is the name of the table. table is an expression of type nvarchar.

'index_name' - Is 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' - Is the name of the database. database is an expression of type nvarchar.

'table' - Is the name of the table.

index_id - Is the ID of the index.

key_id - Is 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 nvarchar (256)
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)

GO

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' - Is 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