SELECT
[Name] = clmns.[name],
[DataType] = usrt.[name],
[SystemType] = ISNULL(baset.[name], N''),
[Length] = CAST(CASE WHEN baset.[name] IN (N'char', N'varchar', N'binary', N'varbinary', N'nchar', N'nvarchar') THEN clmns.prec ELSE clmns.length END AS INT),
[NumericPrecision] = CAST(clmns.[xprec] AS TINYINT),
[NumericScale] = CAST(clmns.[xscale] AS INT),
[Nullable] = CASE CAST(clmns.[isnullable] AS BIT) WHEN 1 THEN 'YES' ELSE 'NO' END,
[DefaultValue] = defaults.[text],
[Identity] = CAST(COLUMNPROPERTY(clmns.[id], clmns.[name], N'IsIdentity') AS INT),
[IsRowGuid] = CAST(COLUMNPROPERTY(clmns.[id], clmns.[name], N'IsRowGuidCol') AS INT),
[IsComputed] = CAST(COLUMNPROPERTY(clmns.[id], clmns.[name], N'IsComputed') AS INT),
[IsDeterministic] = CAST(COLUMNPROPERTY(clmns.[id], clmns.[name], N'IsDeterministic') AS INT),
[IdentitySeed] = CAST(CASE COLUMNPROPERTY(clmns.[id], clmns.[name], N'IsIdentity') WHEN 1 THEN IDENT_SEED(QUOTENAME(stbl.[name]) + '.' + QUOTENAME(tbl.[name])) ELSE 0 END AS NVARCHAR(40)),
[IdentityIncrement] = CAST(CASE COLUMNPROPERTY(clmns.[id], clmns.[name], N'IsIdentity') WHEN 1 THEN IDENT_INCR(QUOTENAME(stbl.[name]) + '.' + QUOTENAME(tbl.[name])) ELSE 0 END AS NVARCHAR(40)),
[ComputedDefinition] = (SELECT [text] FROM dbo.syscomments WHERE [id] = clmns.[id] AND [number] IN (SELECT [colid] FROM dbo.syscolumns WHERE [id] = clmns.[id] AND [name] = clmns.[name] )),
[Collation] = clmns.[collation],
[ConstraintName] = constraints.[ConstraintName],
[ConstraintDef] = constraints.[ConstraintDef],
[ConstraintType] = constraints.[ConstraintType]
FROM
dbo.sysobjects AS tbl
INNER JOIN dbo.sysusers AS stbl ON stbl.[uid] = tbl.[uid]
INNER JOIN dbo.syscolumns AS clmns ON clmns.[id]=tbl.[id]
LEFT OUTER JOIN dbo.systypes AS usrt ON usrt.[xusertype] = clmns.[xusertype]
LEFT OUTER JOIN dbo.sysusers AS sclmns ON sclmns.[uid] = usrt.[uid]
LEFT OUTER JOIN dbo.systypes AS baset ON baset.xusertype = clmns.[xtype] and baset.[xusertype] = baset.[xtype]
LEFT OUTER JOIN dbo.syscomments AS defaults ON defaults.[id] = clmns.[cdefault]
LEFT OUTER JOIN (
SELECT
[ConstraintName] = object_name(const.[constid]),
[ConstraintDef] = CASE WHEN idx.[name] IS NOT NULL THEN idx.[name] ELSE com.[text] END,
[ColumnName] = col.[name],
[ConstraintType] = CASE WHEN idx.[name] IS NOT NULL THEN 'INDEX' WHEN const.[status] & 5 = 5 THEN 'DEFAULT' ELSE 'CHECK' END
FROM
dbo.sysconstraints const
LEFT JOIN dbo.syscomments com ON com.[id] = const.[constid]
LEFT JOIN dbo.syscolumns col ON col.[id] = OBJECT_ID('dbo.Demo') AND col.[colid] = const.[colid]
LEFT JOIN dbo.sysindexes idx ON idx.[id] = OBJECT_ID('dbo.Demo') AND idx.[name] = object_name(const.[constid])
WHERE
const.[id] = OBJECT_ID('dbo.Demo')
AND const.[status] & 7 IN ( 4,5,2 )
) AS constraints (ConstraintName, ConstraintDef, ColumnName, ConstraintType) ON constraints.[ColumnName] = clmns.[name]
WHERE
((tbl.[type] = 'U' OR tbl.[type] = 'S'))
AND (stbl.[name] = N'dbo'
AND tbl.[name] = N'Demo')
ORDER BY
CAST(clmns.[colid] AS INT) ASC
[Name] = clmns.[name],
[DataType] = usrt.[name],
[SystemType] = ISNULL(baset.[name], N''),
[Length] = CAST(CASE WHEN baset.[name] IN (N'char', N'varchar', N'binary', N'varbinary', N'nchar', N'nvarchar') THEN clmns.prec ELSE clmns.length END AS INT),
[NumericPrecision] = CAST(clmns.[xprec] AS TINYINT),
[NumericScale] = CAST(clmns.[xscale] AS INT),
[Nullable] = CASE CAST(clmns.[isnullable] AS BIT) WHEN 1 THEN 'YES' ELSE 'NO' END,
[DefaultValue] = defaults.[text],
[Identity] = CAST(COLUMNPROPERTY(clmns.[id], clmns.[name], N'IsIdentity') AS INT),
[IsRowGuid] = CAST(COLUMNPROPERTY(clmns.[id], clmns.[name], N'IsRowGuidCol') AS INT),
[IsComputed] = CAST(COLUMNPROPERTY(clmns.[id], clmns.[name], N'IsComputed') AS INT),
[IsDeterministic] = CAST(COLUMNPROPERTY(clmns.[id], clmns.[name], N'IsDeterministic') AS INT),
[IdentitySeed] = CAST(CASE COLUMNPROPERTY(clmns.[id], clmns.[name], N'IsIdentity') WHEN 1 THEN IDENT_SEED(QUOTENAME(stbl.[name]) + '.' + QUOTENAME(tbl.[name])) ELSE 0 END AS NVARCHAR(40)),
[IdentityIncrement] = CAST(CASE COLUMNPROPERTY(clmns.[id], clmns.[name], N'IsIdentity') WHEN 1 THEN IDENT_INCR(QUOTENAME(stbl.[name]) + '.' + QUOTENAME(tbl.[name])) ELSE 0 END AS NVARCHAR(40)),
[ComputedDefinition] = (SELECT [text] FROM dbo.syscomments WHERE [id] = clmns.[id] AND [number] IN (SELECT [colid] FROM dbo.syscolumns WHERE [id] = clmns.[id] AND [name] = clmns.[name] )),
[Collation] = clmns.[collation],
[ConstraintName] = constraints.[ConstraintName],
[ConstraintDef] = constraints.[ConstraintDef],
[ConstraintType] = constraints.[ConstraintType]
FROM
dbo.sysobjects AS tbl
INNER JOIN dbo.sysusers AS stbl ON stbl.[uid] = tbl.[uid]
INNER JOIN dbo.syscolumns AS clmns ON clmns.[id]=tbl.[id]
LEFT OUTER JOIN dbo.systypes AS usrt ON usrt.[xusertype] = clmns.[xusertype]
LEFT OUTER JOIN dbo.sysusers AS sclmns ON sclmns.[uid] = usrt.[uid]
LEFT OUTER JOIN dbo.systypes AS baset ON baset.xusertype = clmns.[xtype] and baset.[xusertype] = baset.[xtype]
LEFT OUTER JOIN dbo.syscomments AS defaults ON defaults.[id] = clmns.[cdefault]
LEFT OUTER JOIN (
SELECT
[ConstraintName] = object_name(const.[constid]),
[ConstraintDef] = CASE WHEN idx.[name] IS NOT NULL THEN idx.[name] ELSE com.[text] END,
[ColumnName] = col.[name],
[ConstraintType] = CASE WHEN idx.[name] IS NOT NULL THEN 'INDEX' WHEN const.[status] & 5 = 5 THEN 'DEFAULT' ELSE 'CHECK' END
FROM
dbo.sysconstraints const
LEFT JOIN dbo.syscomments com ON com.[id] = const.[constid]
LEFT JOIN dbo.syscolumns col ON col.[id] = OBJECT_ID('dbo.Demo') AND col.[colid] = const.[colid]
LEFT JOIN dbo.sysindexes idx ON idx.[id] = OBJECT_ID('dbo.Demo') AND idx.[name] = object_name(const.[constid])
WHERE
const.[id] = OBJECT_ID('dbo.Demo')
AND const.[status] & 7 IN ( 4,5,2 )
) AS constraints (ConstraintName, ConstraintDef, ColumnName, ConstraintType) ON constraints.[ColumnName] = clmns.[name]
WHERE
((tbl.[type] = 'U' OR tbl.[type] = 'S'))
AND (stbl.[name] = N'dbo'
AND tbl.[name] = N'Demo')
ORDER BY
CAST(clmns.[colid] AS INT) ASC