探讨SQL利用INFORMATION_SCHEMA系统视图如何获取表的主外键信息

接着上篇文章《解析sql 表结构信息查询 含主外键、自增长》里面提到了information_schema视图，其实到了sql 2005微软都主推大家使用information_schema系统视图，而不是在使用sys东东了，当然目前还是有许多信息只能通过sys视图来查询。这里我们还是以查询表结果信息为例来说明一些主要的information_schema视图的使用。
首先我们需要查询列的信息，这需要用到[information_schema].[columns]系统视图来查询数据列的信息，sql 如下：

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'

运行结果如下：

我们都知道我们在定义列的时候一般的使用都是varchar(50)之类的信息，这里我们需要整合data_type和character_maximum_length信息，当character_maximum_length为-1时即使说没有指定具体最大长度，数据的指定长度信息是max，而numeric需要整合numeric_precision、numeric_scale信息。修改后的sql如下：

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

运行结果如图：

现在我们需要标记这张表的那些列是主键，那些列是外键，要查询表的主、外键信息需要用到[information_schema].[table_constraints]和[information_schema].[key_column_usage] 系统视图
运行结果如图：

修改我们先前的sql语句：

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

运行结果如图：

现在我们已经知道那些列是主键那些是外键，接下来的就是外键列所关联的外检表信息，这里需要用到[information_schema].[referential_constraints]系统视图，运行该视图如下：

所以修改我们的sql如下：

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

运行结果如图：

我们的查询结果中显示了太多的null，看着不怎么舒服，还有我们的表名应该显示一次就可以，修改sql如下：

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

运行结果如图：

有不对的地方还请大家拍砖！