db-schema-migration/export_information_schema.sql at main · Mesh-AI/db-schema-migration · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
SET NOCOUNT ON;
SELECT
C.TABLE_CATALOG,
C.TABLE_SCHEMA,
C.TABLE_NAME,
C.COLUMN_NAME,
K.CONSTRAINT_NAME,
TC.CONSTRAINT_TYPE,

C.ORDINAL_POSITION,
C.COLUMN_DEFAULT,
C.IS_NULLABLE,
C.DATA_TYPE,
C.CHARACTER_MAXIMUM_LENGTH,
C.CHARACTER_OCTET_LENGTH,
C.NUMERIC_PRECISION,
C.NUMERIC_PRECISION_RADIX,
C.NUMERIC_SCALE,
C.DATETIME_PRECISION,
C.CHARACTER_SET_CATALOG,
C.CHARACTER_SET_SCHEMA,
C.CHARACTER_SET_NAME,
C.COLLATION_CATALOG,
C.COLLATION_SCHEMA,
C.COLLATION_NAME,
C.DOMAIN_CATALOG,
C.DOMAIN_SCHEMA,
C.DOMAIN_NAME

FROM TestDB.INFORMATION_SCHEMA.COLUMNS C
    LEFT JOIN TestDB.INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS K ON C.TABLE_CATALOG = K.TABLE_CATALOG
                                                        AND C.TABLE_SCHEMA = K.TABLE_SCHEMA
                                                        AND C.TABLE_NAME = K.TABLE_NAME
                                                        AND C.COLUMN_NAME = K.COLUMN_NAME

    FULL OUTER JOIN TestDB.INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC ON K.TABLE_NAME = TC.TABLE_NAME
                                                        AND K.CONSTRAINT_CATALOG = TC.CONSTRAINT_CATALOG
                                                        AND K.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA
;
GO