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
