Tags

,

Sometimes I want to check the column design for a certain table but don’t want to click the table in object explorer window, then we can use “sp_help ‘SchemaName.TableName” to view all details of the specified table. This SP give you everything.

How about if I just want to view the column design only:

DECLARE @objname as nvarchar(776) = 'schema.table'
DECLARE @objid int
DECLARE @sysobj_type char(2)
SELECT @objid = object_id, @sysobj_type = type from sys.all_objects where object_id = object_id(@objname)
DECLARE  @dbname  sysname, @no varchar(35), @yes varchar(35), @none varchar(35)
SELECT @no = 'no', @yes = 'yes', @none = 'none'
DECLARE @precscaletypes nvarchar(150)
    SELECT @precscaletypes = N'tinyint,smallint,decimal,int,bigint,real,money,float,numeric,smallmoney,date,time,datetime2,datetimeoffset,'

    -- INFO FOR EACH COLUMN
    print ' '
    SELECT
      'Column_name'      = name,
      'Type'          = type_name(user_type_id),
      'Computed'        = case when ColumnProperty(object_id, name, 'IsComputed') = 0 then @no else @yes end,
      'Length'          = convert(int, max_length),
      -- for prec/scale, only show for those types that have valid precision/scale
      -- Search for type name + ',', because 'datetime' is actually a substring of 'datetime2' and 'datetimeoffset'
      'Prec'          = case when charindex(type_name(system_type_id) + ',', @precscaletypes) > 0
                    then convert(char(5),ColumnProperty(object_id, name, 'precision'))
                    else '     ' end,
      'Scale'          = case when charindex(type_name(system_type_id) + ',', @precscaletypes) > 0
                    then convert(char(5),OdbcScale(system_type_id,scale))
                    else '     ' end,
      'Nullable'        = case when is_nullable = 0 then @no else @yes end,
      'TrimTrailingBlanks'  = case ColumnProperty(object_id, name, 'UsesAnsiTrim')
                    when 1 then @no
                    when 0 then @yes
                    else '(n/a)' end,
      'FixedLenNullInSource'  = case
            when type_name(system_type_id) not in ('varbinary','varchar','binary','char')
              then '(n/a)'
            when is_nullable = 0 then @no else @yes end,
      'Collation'    = collation_name
    from sys.all_columns where object_id = @objid
Advertisements