Meta Data User-Defined Functions

January 16, 2001


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









    The Network for Technology Professionals

    Search:

    About Internet.com

    Legal Notices, Licensing, Permissions, Privacy Policy.
    Advertise | Newsletters | E-mail Offers