Binaryworld - Meta Data User-Defined Functions ... [ SQL Server -> User Defined Function ]

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)