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]“

[Crosss-Post from my other blog http://dbalink.wordpress.com/ - Marlon Ribunal]