Get some information about the database table:
IF
?
exists
(
SELECT
?
*
?
From
? DBO.SYSOBJECTS?
WHERE
? id?
=
?
Object_id
(N
'
[DBO]. [getDatabaseschematerial]
'
)?
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?
AS
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.characper_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
