Obtain some information about the database table:
If exists (select * from dbo. sysobjects where id = object_id (n' [dbo]. [GetDatabaseSchemaInformation] ') and OBJECTPROPERTY (id, n' IsProcedure') = 1)
Drop procedure [dbo]. [GetDatabaseSchemaInformation]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE Procedure dbo. GetDatabaseSchemaInformation
Select cols. TABLE_NAME As 'table name ',
Cols. COLUMN_NAME as 'column name ',
Cols. DATA_TYPE as 'data type ',
Case
When cols. DATA_TYPE = 'bigint' then '8 bytes'
When cols. DATA_TYPE = 'int' then '4 bytes'
When cols. DATA_TYPE = 'smallint' then '2 bytes'
When cols. DATA_TYPE = 'tinyint' then '1 byte'
When cols. DATA_TYPE = 'bit' then '1 byte'
When cols. DATA_TYPE = 'money' then '8 bytes'
When cols. DATA_TYPE = 'smallmoney' then '4 bytes'
When cols. DATA_TYPE = 'text' then '16 bytes'
When cols. DATA_TYPE = 'datetime' then '8 bytes'
When cols. DATA_TYPE = 'smalldatetime' then '4 bytes'
When cols. DATA_TYPE = 'uniqueidentifier' then '16 bytes'
When cols. DATA_TYPE = 'decimal' then convert (varchar, cols. NUMERIC_PRECISION) + ',' + convert (varchar, cols. NUMERIC_SCALE)
Else convert (varchar, cols. CHARACTER_MAXIMUM_LENGTH) + 'chars'
End as 'length ',
Case
When kcu. CONSTRAINT_NAME is null then 'no' else 'yes'
End as 'pk? ',
Upper (cols. IS_NULLABLE) as 'allows nulls ',
Isnull (cols. COLUMN_DEFAULT, '') as 'default value'
From INFORMATION_SCHEMA.COLUMNS cols
Join INFORMATION_SCHEMA.TABLES tabs on cols. TABLE_NAME = tabs. TABLE_NAME and
Tabs. TABLE_TYPE = 'base table'
Left join INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE ctu on cols. TABLE_NAME = ctu. TABLE_NAME and ctu. CONSTRAINT_NAME like 'pk %'
Left join INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu on ctu. CONSTRAINT_NAME =
Kcu. CONSTRAINT_NAME and cols. COLUMN_NAME = kcu. COLUMN_NAME
Where tabs. TABLE_NAME <> 'dtproperties'
Order by cols. TABLE_NAME, cols. ORDINAL_POSITION
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
