Explore how SQL uses INFORMATION_SCHEMA system view to obtain the table's primary and foreign key information _ MySQL

Explore how SQL uses INFORMATION_SCHEMA system view to obtain the table's primary and foreign key information bitsCN.com 《 Parsing SQL table structure information query includes primary and foreign keys, auto-incrementThe INFORMATION_SCHEMA view is mentioned in this article. In fact, in SQL 2005, Microsoft has promoted the use of the INFORMATION_SCHEMA system view, rather than using sys. of course, there is still a lot of information that can only be queried through the sys view. Here we take the query table result as an example to illustrate the use of some major INFORMATION_SCHEMA views.
First, we need to query the column information, which requires the [INFORMATION_SCHEMA]. [COLUMNS] system view to query the information of the data column. the SQL is as follows:

SELECT c. TABLE_SCHEMA,
C. TABLE_NAME,
C. COLUMN_NAME,
C. DATA_TYPE,
C. CHARACTER_MAXIMUM_LENGTH,
C. COLUMN_DEFAULT,
C. IS_NULLABLE,
C. NUMERIC_PRECISION,
C. NUMERIC_SCALE
FROM [INFORMATION_SCHEMA]. [COLUMNS] c
WHERE TABLE_NAME = 'address'

The running result is as follows:

We all know that we generally use varchar (50) and other information when defining columns. here we need to integrate the DATA_TYPE and CHARACTER_MAXIMUM_LENGTH information, when CHARACTER_MAXIMUM_LENGTH is-1, even if the maximum length is not specified, the specified length of data is max, while numeric needs to integrate NUMERIC_PRECISION and NUMERIC_SCALE information. The modified SQL statement is as follows:

SELECT c. TABLE_SCHEMA,
C. TABLE_NAME,
C. COLUMN_NAME,
Case when (CHARINDEX ('char ', c. DATA_TYPE)> 0
Or charindex ('binary ', c. DATA_TYPE)> 0
)
AND c. CHARACTER_MAXIMUM_LENGTH <>-1
)
THEN c. DATA_TYPE + '('
+ CAST (c. CHARACTER_MAXIMUM_LENGTH as varchar (4) + ')'
WHEN (CHARINDEX ('char ', c. DATA_TYPE)> 0
Or charindex ('binary ', c. DATA_TYPE)> 0
)
AND c. CHARACTER_MAXIMUM_LENGTH =-1
) THEN c. DATA_TYPE + '(max )'
WHEN (CHARINDEX ('numeric ', c. DATA_TYPE)> 0)
THEN c. DATA_TYPE + '(' + CAST (c. NUMERIC_PRECISION as varchar (4 ))
+ ',' + CAST (c. NUMERIC_SCALE as varchar (4) + ')'
ELSE c. DATA_TYPE
End as DATA_TYPE,
C. COLUMN_DEFAULT,
C. IS_NULLABLE,
C. COLUMN_DEFAULT
FROM [INFORMATION_SCHEMA]. [COLUMNS] c
WHERE TABLE_NAME = 'address'
Order by c. ORDINAL_POSITION

Running result

Now we need to mark the columns in this table as primary keys and those columns as foreign keys. to query the table's primary and foreign key information, we need to use [INFORMATION_SCHEMA]. [TABLE_CONSTRAINTS] and [INFORMATION_SCHEMA]. [KEY_COLUMN_USAGE] system View
Running result

Modify our previous SQL statement:

SELECT c. TABLE_SCHEMA,
C. TABLE_NAME,
C. COLUMN_NAME,
Case when (CHARINDEX ('char ', c. DATA_TYPE)> 0
Or charindex ('binary ', c. DATA_TYPE)> 0
)
AND c. CHARACTER_MAXIMUM_LENGTH <>-1
)
THEN c. DATA_TYPE + '('
+ CAST (c. CHARACTER_MAXIMUM_LENGTH as varchar (4) + ')'
WHEN (CHARINDEX ('char ', c. DATA_TYPE)> 0
Or charindex ('binary ', c. DATA_TYPE)> 0
)
AND c. CHARACTER_MAXIMUM_LENGTH =-1
) THEN c. DATA_TYPE + '(max )'
WHEN (CHARINDEX ('numeric ', c. DATA_TYPE)> 0)
THEN c. DATA_TYPE + '(' + CAST (c. NUMERIC_PRECISION as varchar (4 ))
+ ',' + CAST (c. NUMERIC_SCALE as varchar (4) + ')'
ELSE c. DATA_TYPE
End as DATA_TYPE,
C. COLUMN_DEFAULT,
C. IS_NULLABLE,
C. COLUMN_DEFAULT,
Case when tc. CONSTRAINT_TYPE = 'primary key' THEN 'yes'
ELSE 'no'
End as IS_PRIMARY_KEY,
Case when tc. CONSTRAINT_TYPE = 'foreign key' THEN 'yes'
ELSE 'no'
End as IS_FOREIGN_KEY
FROM [INFORMATION_SCHEMA]. [COLUMNS] c
Left join [INFORMATION_SCHEMA]. [KEY_COLUMN_USAGE] kcu ON kcu. TABLE_SCHEMA = c. TABLE_SCHEMA
AND kcu. TABLE_NAME = c. TABLE_NAME
AND kcu. COLUMN_NAME = c. COLUMN_NAME
Left join [INFORMATION_SCHEMA]. [TABLE_CONSTRAINTS] tc ON tc. CONSTRAINT_SCHEMA = kcu. CONSTRAINT_SCHEMA
AND tc. CONSTRAINT_NAME = kcu. CONSTRAINT_NAME
WHERE c. TABLE_NAME = 'address'
Order by c. ORDINAL_POSITION

Running result

Now we know that the columns are primary keys and those are foreign keys. next we will use the external checklist information associated with the foreign key column. here we need to use [INFORMATION_SCHEMA]. [REFERENTIAL_CONSTRAINTS] system View. run this view as follows:

Therefore, modify the SQL statement as follows:

SELECT c. TABLE_SCHEMA,
C. TABLE_NAME,
C. COLUMN_NAME,
Case when (CHARINDEX ('char ', c. DATA_TYPE)> 0
Or charindex ('binary ', c. DATA_TYPE)> 0
)
AND c. CHARACTER_MAXIMUM_LENGTH <>-1
)
THEN c. DATA_TYPE + '('
+ CAST (c. CHARACTER_MAXIMUM_LENGTH as varchar (4) + ')'
WHEN (CHARINDEX ('char ', c. DATA_TYPE)> 0
Or charindex ('binary ', c. DATA_TYPE)> 0
)
AND c. CHARACTER_MAXIMUM_LENGTH =-1
) THEN c. DATA_TYPE + '(max )'
WHEN (CHARINDEX ('numeric ', c. DATA_TYPE)> 0)
THEN c. DATA_TYPE + '(' + CAST (c. NUMERIC_PRECISION as varchar (4 ))
+ ',' + CAST (c. NUMERIC_SCALE as varchar (4) + ')'
ELSE c. DATA_TYPE
End as DATA_TYPE,
C. COLUMN_DEFAULT,
C. IS_NULLABLE,
C. COLUMN_DEFAULT,
Case when tc. CONSTRAINT_TYPE = 'primary key' THEN 'yes'
ELSE 'no'
End as IS_PRIMARY_KEY,
Case when tc. CONSTRAINT_TYPE = 'foreign key' THEN 'yes'
ELSE 'no'
End as IS_FOREIGN_KEY,
Fkcu. COLUMN_NAME AS FOREIGN_KEY,
Fkcu. TABLE_NAME AS FOREIGN_TABLE
FROM [INFORMATION_SCHEMA]. [COLUMNS] c
Left join [INFORMATION_SCHEMA]. [KEY_COLUMN_USAGE] kcu ON kcu. TABLE_SCHEMA = c. TABLE_SCHEMA
AND kcu. TABLE_NAME = c. TABLE_NAME
AND kcu. COLUMN_NAME = c. COLUMN_NAME
Left join [INFORMATION_SCHEMA]. [TABLE_CONSTRAINTS] tc ON tc. CONSTRAINT_SCHEMA = kcu. CONSTRAINT_SCHEMA
AND tc. CONSTRAINT_NAME = kcu. CONSTRAINT_NAME
Left join [INFORMATION_SCHEMA]. [REFERENTIAL_CONSTRAINTS] fc ON kcu. CONSTRAINT_SCHEMA = fc. CONSTRAINT_SCHEMA
AND kcu. CONSTRAINT_NAME = fc. CONSTRAINT_NAME
Left join [INFORMATION_SCHEMA]. [KEY_COLUMN_USAGE] fkcu ON fkcu. CONSTRAINT_SCHEMA = fc. UNIQUE_CONSTRAINT_SCHEMA
AND fkcu. CONSTRAINT_NAME = fc. UNIQUE_CONSTRAINT_NAME
WHERE c. TABLE_NAME = 'address'
Order by c. ORDINAL_POSITION

Running result

The query results show too many NULL values, which are not very comfortable. we can also display the table name once. modify the SQL statement as follows:

Select case when c. ORDINAL_POSITION = 1
THEN c. TABLE_SCHEMA + '.' + c. TABLE_NAME
ELSE''
End as TABLE_NAME,
C. COLUMN_NAME,
Case when (CHARINDEX ('char ', c. DATA_TYPE)> 0
Or charindex ('binary ', c. DATA_TYPE)> 0
)
AND c. CHARACTER_MAXIMUM_LENGTH <>-1
)
THEN c. DATA_TYPE + '('
+ CAST (c. CHARACTER_MAXIMUM_LENGTH as varchar (4) + ')'
WHEN (CHARINDEX ('char ', c. DATA_TYPE)> 0
Or charindex ('binary ', c. DATA_TYPE)> 0
)
AND c. CHARACTER_MAXIMUM_LENGTH =-1
) THEN c. DATA_TYPE + '(max )'
WHEN (CHARINDEX ('numeric ', c. DATA_TYPE)> 0)
THEN c. DATA_TYPE + '(' + CAST (c. NUMERIC_PRECISION as varchar (4 ))
+ ',' + CAST (c. NUMERIC_SCALE as varchar (4) + ')'
ELSE c. DATA_TYPE
End as DATA_TYPE,
ISNULL (c. COLUMN_DEFAULT, '') AS COLUMN_DEFAULT,
Case when c. IS_NULLABLE = 'yes' then' √'
ELSE''
END IS_NULLABLE,
Case when tc. CONSTRAINT_TYPE = 'primary KEY 'then' √'
ELSE''
End as IS_PRIMARY_KEY,
Case when tc. CONSTRAINT_TYPE = 'foreign key' then' √'
ELSE''
End as IS_FOREIGN_KEY,
ISNULL (fkcu. COLUMN_NAME, '') AS FOREIGN_KEY,
ISNULL (fkcu. TABLE_NAME, '') AS FOREIGN_TABLE
FROM [INFORMATION_SCHEMA]. [COLUMNS] c
Left join [INFORMATION_SCHEMA]. [KEY_COLUMN_USAGE] kcu ON kcu. TABLE_SCHEMA = c. TABLE_SCHEMA
AND kcu. TABLE_NAME = c. TABLE_NAME
AND kcu. COLUMN_NAME = c. COLUMN_NAME
Left join [INFORMATION_SCHEMA]. [TABLE_CONSTRAINTS] tc ON tc. CONSTRAINT_SCHEMA = kcu. CONSTRAINT_SCHEMA
AND tc. CONSTRAINT_NAME = kcu. CONSTRAINT_NAME
Left join [INFORMATION_SCHEMA]. [REFERENTIAL_CONSTRAINTS] fc ON kcu. CONSTRAINT_SCHEMA = fc. CONSTRAINT_SCHEMA
AND kcu. CONSTRAINT_NAME = fc. CONSTRAINT_NAME
Left join [INFORMATION_SCHEMA]. [KEY_COLUMN_USAGE] fkcu ON fkcu. CONSTRAINT_SCHEMA = fc. UNIQUE_CONSTRAINT_SCHEMA
AND fkcu. CONSTRAINT_NAME = fc. UNIQUE_CONSTRAINT_NAME
WHERE c. TABLE_NAME = 'address'
Order by c. ORDINAL_POSITION


Running result

If something is wrong, please make a brick! BitsCN.com