List all the fields of a table with its properties + PK

,

First: set @table_name

The script returns the next fields:

  • name: the column name
  • column_id: internal id of the column
  • size
  • precision
  • scale
  • is_identity: 1 indicates it's a identity field
  • is_nullable
  • type: (int, varchar, etc...)
  • user_type: user defined type
  • pk_ordinal: the ordinal if it's part of the PK

I use this script to automate the construction of all the procedures of a new table, view, insert, update, delete, select, ...

-- 
-- set @TableName before to use it
--
-- thanks to janis.l.murphy for adding schema name to the script
--

declare 
	@ObjectFilter varchar(128)
	,@SchemaName varchar(28)
	,@TableName varchar(100)
	
set @ObjectFilter = 'your_table_name'; --Can be in the form on [schema name].[table name]
set @TableName = parsename( @ObjectFilter,1); -- Captures the unquoted table name
set @SchemaName = parsename( @ObjectFilter,2); -- Captures the unquoted schema name; NULL is handled

select 
	cols.name
	,cols.column_id
	,cols.max_length as size
	,cols.precision
	,cols.scale
	,cols.is_identity
	,cols.is_nullable
	,tipus.name as [type]
	,domain.name as [user_type]
	,(select key_ordinal 
	  from sys.index_columns as ic
	  where 
		ic.object_id = (select parent_object_id 
			        from sys.key_constraints 
			        where type = 'PK' 
			        and parent_object_id = cols.object_id)
		and ic.index_id = (select unique_index_id 
				from sys.key_constraints 
				where type = 'PK' 
				and parent_object_id = cols.object_id)
		and ic.column_id = cols.column_id) as pk_ordinal
from 
	sys.columns as cols
	left join sys.types as tipus
		on tipus.system_type_id = cols.system_type_id 
		and tipus.user_type_id = cols.system_type_id 
		and tipus.is_user_defined = 0
	left join sys.types as domain
		on domain.user_type_id = cols.user_type_id 
		and domain.is_user_defined = 1
where cols.object_id = (select object_id 
		      from sys.tables 
		      where name = @TableName
		      and (@SchemaName is null or object_schema_name(object_id) = @SchemaName)
		      )
order by cols.column_id

Rate

3.33 (3)

Share

Share

Rate

3.33 (3)