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

    Alexander Chigrik
    Alexander Chigrik
    I am the owner of MSSQLCity.Com - a site dedicated to providing useful information for IT professionals using Microsoft SQL Server. This site contains SQL Server Articles, FAQs, Scripts, Tips and Test Exams.

    Latest Articles