Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Jan 16, 2001

Meta Data User-Defined Functions

By Alexander Chigrik


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




    MS SQL Archives

    Comment and Contribute

     


    (Maximum characters: 1200). You have characters left.

     

     




    Latest Forum Threads
    MS SQL Forum
    Topic By Replies Updated
    SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
    Need help changing table contents nkawtg 1 August 17th, 03:02 AM
    SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
    SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM