info@techdevops.com
TechDevOps.com
Explore Tools
Experts in Microsoft SQL Server Operations, Performance, Replication, Cloud & Migrations



T-SQL - Get Tables, Fields & Data Types
by BF (Senior MSSQL Operations - Enterprise, Cloud, Strategy)
2017-07-24









T-SQL - Get Tables, Fields & Data Types:

SELECT
o.type_desc as 'Type',
UPPER(OBJECT_SCHEMA_NAME (c.object_id)) 'Schema',
UPPER(o.Name) AS 'Table',
o.create_date as 'Created',
o.modify_date as 'Modified',
c.Name AS 'Field',
t.Name AS 'DataType',
c.max_length AS 'MaxLength',
t.precision AS 'Precision',
o.is_ms_shipped as 'IsMSShipped',
c.is_nullable as 'IsNullable',
c.is_identity as 'IsIdentity',
c.collation_name as 'Collation'
FROM
sys.columns c
INNER JOIN sys.objects o on o.object_id = c.object_id
LEFT JOIN sys.types t on t.user_type_id = c.user_type_id
WHERE
o.type = 'U'
and
o.is_ms_shipped = 0
ORDER BY
o.Name--, c.Name