Meta Data User-Defined Functions

Introduction

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

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.