Querying the Object Catalog and Information Schema Views

October 24, 2008

Okay, these are at least the two ways you can query the Metadata in SQL Server (TSQL). The first one is like what I’ve already shown here, which has the following TSQL code:

SELECT T.NAME AS [TABLE NAME], C.NAME AS [COLUMN NAME], P.NAME AS [DATA TYPE], P.MAX_LENGTH AS[SIZE], CAST(P.PRECISION AS VARCHAR) +‘/’+ CAST(P.SCALE AS VARCHAR) AS [PRECISION/SCALE]
FROM ADVENTUREWORKS.SYS.OBJECTS AS T
JOIN ADVENTUREWORKS.SYS.COLUMNS AS C
ON T.OBJECT_ID=C.OBJECT_ID
JOIN ADVENTUREWORKS.SYS.TYPES AS P
ON C.SYSTEM_TYPE_ID=P.SYSTEM_TYPE_ID
WHERE T.TYPE_DESC=‘USER_TABLE’;

The TSQL above is querying the Object Catalog Views. For like any other querying tasks in SQL Server, there are lots of options in doing the same thing. To achieve a similar result as what the above TSQL will return, we can query the SQL Server Information Schema Views. Here’s how:

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION,
       COLUMN_DEFAULT, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
       NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE,
       DATETIME_PRECISION
FROM ADVENTUREWORKS.INFORMATION_SCHEMA.COLUMNS

The Object Catalog Views contain the information about the database objects such as partitions, procedures, constraints, events, tables, views, triggers, etc.

The Information Schema Views are also another way of looking at the same metadata. “Information schema views provide an internal, system table-independent view of the SQL Server metadata. Information schema views enable applications to work correctly although significant changes have been made to the underlying system tables. The information schema views included in SQL Server comply with the ISO standard definition for the INFORMATION_SCHEMA. [MSDN]”

2 Comments leave one →

YordanGeorgiev permalink

February 12, 2009 12:14 PM

USE [db]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create proc [dbo].[procUtilsMeta_GetProcMetaData]
@domain_user varchar(200) ,
@ret int OUT,
@msg varchar(200) = null out ,
@procName varchar(200)

as
declare @debugmsg varchar(1000)

begin –proc

begin try

select PARAMETER_NAME as ‘COLUMN_NAME’, DATA_TYPE , CHARACTER_MAXIMUM_LENGTH AS ‘MAX_LENGTH’, IS_RESULT , PARAMETER_MODE from INFORMATION_SCHEMA.PARAMETERS where SPECIFIC_NAME=@procName

set @msg = ‘ meta data for procedure ‘ + @procName + ‘ retrieved successfully’
set @ret =0

END TRY

BEGIN CATCH
PRINT ‘In CATCH block.
Error number: ‘ + CAST(ERROR_NUMBER() AS varchar(10)) + ‘
Error message: ‘ + ERROR_MESSAGE() + ‘
Error severity: ‘ + CAST(ERROR_SEVERITY() AS varchar(10)) + ‘
Error state: ‘ + CAST(ERROR_STATE() AS varchar(10)) + ‘
XACT_STATE: ‘ + CAST(XACT_STATE() AS varchar(10));

set @msg = ‘ Failed to retrieve meta data for procedure ‘ + @procName
set @debugmsg = ‘: Error number: ‘ + CAST(ERROR_NUMBER() AS varchar(10)) +
‘Error message: ‘ + ERROR_MESSAGE() + ‘Error severity: ‘ +
CAST(ERROR_SEVERITY() AS varchar(10)) + ‘Error state: ‘ +
CAST(ERROR_STATE() AS varchar(10)) + ‘XACT_STATE: ‘ +
CAST(XACT_STATE() AS varchar(10))

set @ret = 1

END CATCH
end –proc

Reply