A Few Meta Data UDFs

,


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

Rate

Share

Share

Rate