Getting Table and Column details

When you want to generate T-SQL code (views, procedures, triggers) automatically you need a list of tables and their columns.  It’s useful to have these in temporary tables or table variables because it is usually necessary to iterate through column collections more than once.

This block of code fills @Tables and @Columns with user tables and their columns.  condensed_type and nullable columns can be used directly in type declarations (e.g. in procedure parameter collections).  Other flags are useful in structuring a procedure – for example, computed and identity columns cannot be updated.

/*
	Defines two table variables and populates them with all user tables and their columns.

	@tables
		table_id		: a surrogate key for the table.
		table_object_id : database ID for the table - from sys.objects.
		schema_name		: schema that the table is in (without brackets).
		table_name		: table name (without brackets).
	@columns
		column_id		: a surrogate key for the table.
		table_object_id	: database ID for the table (FK to @tables.table_object_id).
		column_name		: name of column (without brackets).
		nullable		: 'NULL' if nullable, else '' (change to 'NOT NULL' if required).
		condensed_type	: column type in form suitable for declarations.
		primary_key_flag: 'Y' = this is the primary key.
		identity_flag	: 'Y' = this is an identity column.
		computed_column_flag : 'Y' = this is a computed column.

	28/4/2014 www.andrewing.co.uk 
*/

	DECLARE @Tables TABLE (
		table_id int IDENTITY(1,1)
		,table_object_id int
		,schema_name varchar(200)
		,table_name varchar(200)
	)

	DECLARE @Columns TABLE(
		column_id int IDENTITY(1,1)
		,table_object_id int NOT NULL
		,column_name varchar(200)
		,condensed_type varchar(50) NOT NULL
		,nullable varchar(8) NOT NULL
		,primary_key_flag char(1) NOT NULL
		,identity_flag char(1) NOT NULL
		,computed_column_flag char(1) NOT NULL
	)
	/* Populate Tables */
	INSERT INTO @Tables (
		table_object_id
		,schema_name
		,table_name
		)
	SELECT O.object_id
		,S.NAME AS schema_name
		,O.NAME AS table_name
	FROM sys.objects O
	INNER JOIN sys.schemas S ON S.schema_id = O.schema_id
	WHERE O.type = 'U'
	ORDER BY schema_name
		,table_name
	/* Populate Columns */
	INSERT INTO @Columns (
		table_object_id
		,column_name
		,nullable
		,condensed_type
		,primary_key_flag
		,identity_flag
		,computed_column_flag
		)
	SELECT T.table_object_id
		,C.NAME
		,CASE 
			WHEN C.is_nullable = 1
				THEN 'NULL'
			ELSE ''
			END
		,type_name(C.user_type_id) + CASE 
			WHEN (type_name(C.user_type_id) IN ('varchar', 'char'))
				THEN '(' + cast(C.max_length AS VARCHAR) + ')'
			WHEN (type_name(C.user_type_id) IN ('nvarchar', 'nchar'))
				THEN '(' + cast(C.max_length / 2 AS VARCHAR) + ')'
			WHEN type_name(C.user_type_id) = 'decimal'
				THEN '(' + cast(C.[precision] AS VARCHAR) + ',' + cast(C.scale AS VARCHAR) + ')'
			ELSE ''
			END
		,ISNULL(PK.IsPrimaryKey, 'N')
		,CASE 
			WHEN C.is_identity = 1
				THEN 'Y'
			ELSE 'N'
			END
		,CASE 
			WHEN C.is_computed = 1
				THEN 'Y'
			ELSE 'N'
			END
	FROM sys.columns C
	INNER JOIN @Tables T ON C.object_id = T.table_object_id
	LEFT JOIN (
		SELECT ic.column_id
			,ic.object_id
			,'Y' AS IsPrimaryKey
		FROM sys.index_columns IC
		INNER JOIN sys.key_constraints KC ON KC.parent_object_id = ic.object_id
			AND IC.index_id = KC.unique_index_id
		WHERE KC.type = 'PK'
		) PK ON PK.object_id = C.object_id
		AND PK.column_id = C.column_id
	ORDER BY C.object_id

	UPDATE @Columns SET condensed_type = REPLACE(condensed_type, '(-1)', '(max)')
         UPDATE @Columns SET condensed_type = REPLACE(condensed_type, '(0)', '(max)')

	/* Test output */

	SELECT * FROM @Tables
	SELECT * FROM @Columns

Generating the Text

The weakness of this method is that the text construction loops don’t always make for great readability.   Each line of code is inserted as a row in table @Sql.  An IDENTITY column ensures that the rows can be extracted in order.

There is an outer loop of all tables where table information is placed into variables.  Each iteration of this loop will create DDL for a complete object (or several objects).  Within the loop, column lookups can be run at any time.  This is necessary because it is often necessary to create lists of columns several times within a procedure definition, sometimes with different criteria  (E.g. the Parameter list of an update proc will contain the primary key whereas the update code won’t).

This example code creates a simple Select view for each user table (more likely such views would be created in a different schema). This is fairly clear to follow; stored procedures would need two or perhaps three iterations of the columns.

DECLARE @Sql TABLE(
		 row_id int IDENTITY
		,sql_text varchar(2000))

	DECLARE @TableCardinal int = 1
	DECLARE @TableName varchar(200), @SchemaName varchar(200),  @TableObjectId int
	WHILE (@TableCardinal <= (SELECT MAX(table_id) FROM @Tables))
	BEGIN
		SELECT	@TableObjectId = table_object_id,
				@SchemaName = schema_name,
				@TableName =  table_name
		FROM	@Tables
		WHERE	table_id = @TableCardinal

		INSERT INTO @Sql(sql_text) VALUES
			('/* Auto-Generated */'),
			(''),
			('Create View ' + @SchemaName + '.' + @TableName + '_View'),
			('AS'),
			(''),
			('  SELECT')

		INSERT INTO @Sql(sql_text) 
			SELECT CASE WHEN ROW_NUMBER() OVER (ORDER BY column_id) = 1 THEN '    [' ELSE '   ,[' END +
				column_name + ']'
			FROM @Columns
			WHERE table_object_id = @TableObjectId

		INSERT INTO @Sql (sql_text) VALUES
			('  FROM ' + @SchemaName + '.' + @TableName),
			(''),
			('GO'),
			('')

		SET @TableCardinal += 1
	END

	SELECT sql_text from @SQL ORDER BY sql_id