USE master
GO
IF OBJECT_ID('sp_Column') IS NULL EXEC ('CREATE PROCEDURE sp_Column AS SELECT 1 AS ID')
GO
ALTER PROCEDURE dbo.sp_Column
@ObjectName SYSNAME = NULL
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @ObjectId INT = OBJECT_ID(Util.dbo.TrimBothEnds(@ObjectName))
IF @ObjectId IS NULL
BEGIN
EXEC master.dbo.sp_ExecTemplate
@ObjectName = 'sp_Column',
@PrintDefault = 0,
@DeclareReturn = 0,
@PrintReturnLine = 0,
@DoNotDeclareVar = 1
RETURN
END
SELECT c.column_id AS ColumnId,
c.name AS ColumnName,
ct.ColumnType AS ColumnDef,
ic.key_ordinal AS PKOrdinal,
c.is_nullable AS IsNullable,
c.is_identity AS IsIdentity,
c.max_length AS MaxLength,
c.is_computed AS IsComputed,
dc.definition AS DefaultDefinition,
ck.definition AS CheckDefinition,
cc.DEFINITION AS ComputeDefinition
FROM sys.schemas s (NOLOCK)
INNER JOIN sys.objects o (NOLOCK) ON s.SCHEMA_ID = o.SCHEMA_ID
INNER JOIN sys.columns c (NOLOCK) ON c.OBJECT_ID = o.OBJECT_ID
LEFT OUTER JOIN Util.dbo.SystemObjectTypes ot ON ot.Type = o.TYPE COLLATE SQL_Latin1_General_CP1_CI_AS
INNER JOIN sys.types y (NOLOCK) ON y.user_type_id = c.user_type_id
INNER JOIN sys.databases AS db (NOLOCK) ON db.database_id = DB_ID()
LEFT OUTER JOIN sys.indexes i (NOLOCK) ON i.OBJECT_ID = o.OBJECT_ID
AND i.is_primary_key = 1
LEFT OUTER JOIN sys.index_columns ic (NOLOCK) ON ic.OBJECT_ID = o.OBJECT_ID
AND ic.index_id = i.index_id
AND ic.column_id = c.column_id
LEFT OUTER JOIN sys.default_constraints dc (NOLOCK) ON dc.parent_object_id = o.OBJECT_ID
AND dc.parent_column_id = c.column_id
LEFT OUTER JOIN sys.check_constraints ck (NOLOCK) ON ck.parent_object_id = o.OBJECT_ID
AND ck.parent_column_id = c.column_id
LEFT OUTER JOIN sys.computed_columns cc (NOLOCK) ON cc.OBJECT_ID = o.OBJECT_ID
AND cc.column_id = c.column_id
CROSS APPLY Util.dbo.GetColumnType(y.name, c.max_length, c.PRECISION, c.scale, c.collation_name, db.collation_name) ct
WHERE o.OBJECT_ID = @ObjectId
OPTION (RECOMPILE)
GO
EXEC sys.sp_ms_marksystemobject sp_Column
GO