Meta Data User-Defined Functions

Introduction

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

Download Meta Data User-Defined Functions.

» See All Articles by Columnist Alexander Chigrik