Meta Data User-Defined Functions

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.
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