Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

A Few Meta Data UDFs

By Alexander Chigrik,



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

Total article views: 4030 | Views in the last 30 days: 3
 
Related Articles
FORUM

Index on BIT column

Index on BIT column

FORUM

Return result of dynamic query from function

Return result of dynamic query from function

ARTICLE

The Cost of Function Use In A Where Clause

Discusses Index Selection impact when functions are wrapped around WHERE clause filtering columns

FORUM

user defined functions & columns

user defined functions & columns

FORUM

Help Needed - Function Returning Table

Function Returning Table

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones