Meta Data User-Defined Functions

Introduction

Meta Data UDFs

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:
__ATLAS_TABLE_BLOCK__0__
Examples
This example returns the defined length (in bytes) of the au_id
column
of the authors table in the pubs database:
__ATLAS_TABLE_BLOCK__1__
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:
__ATLAS_TABLE_BLOCK__2__
Examples
This example returns the ID of the au_fname column of the
authors table in the pubs database:
__ATLAS_TABLE_BLOCK__3__
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:
__ATLAS_TABLE_BLOCK__4__
Examples
This example returns the ID of the aunmind index of the
authors table in the pubs database:
__ATLAS_TABLE_BLOCK__5__
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:
__ATLAS_TABLE_BLOCK__6__
Examples
This example returns the indexed column name of the authors table
in the pubs database (for index_id = 2 and key_id = 1):
__ATLAS_TABLE_BLOCK__7__
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:
__ATLAS_TABLE_BLOCK__8__
Examples
This example returns the total row count of the authors table
in the pubs database:
__ATLAS_TABLE_BLOCK__9__
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 ColumnistAlexander Chigrik