Tuesday, July 7, 2009

TSQL : Tables, Columns and Datatypes

I wanted to locate tables with datetime columns to evaluate my partitioning approach. This this the code I used.

Tables, Columns and Datatypes

SELECT
   sys_schemas.Name as [schema_name]
 , sys_tables.Name AS [table_name]
 , sys_columns.Name AS [column_name]
 , sys_types.Name AS [datatype_name]
FROM Sys.Tables sys_tables
JOIN Sys.Schemas sys_schemas
ON sys_schemas.Schema_Id = sys_tables.Schema_Id
JOIN Sys.Columns sys_columns
ON sys_columns.Object_Id = sys_tables.Object_Id
JOIN Sys.Types sys_types
ON sys_types.System_Type_Id = sys_columns.System_Type_Id
WHERE sys_types.Name = 'datetime'
ORDER BY sys_schemas.Name, sys_tables.Name, sys_columns.Name, sys_types.Name

No comments: