欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2024-02-22 10:34:52
接着上篇文章《解析sql 表结构信息查询 含主外键、自增长》里面提到了information_schema视图,其实到了sql 2005微软都主推大家使用informati...
接着上篇文章《解析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'

运行结果如下:
探讨SQL利用INFORMATION_SCHEMA系统视图如何获取表的主外键信息
我们都知道我们在定义列的时候一般的使用都是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

运行结果如图:
探讨SQL利用INFORMATION_SCHEMA系统视图如何获取表的主外键信息
现在我们需要标记这张表的那些列是主键,那些列是外键,要查询表的主、外键信息需要用到[information_schema].[table_constraints]和[information_schema].[key_column_usage] 系统视图
运行结果如图:
探讨SQL利用INFORMATION_SCHEMA系统视图如何获取表的主外键信息
修改我们先前的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

运行结果如图:
探讨SQL利用INFORMATION_SCHEMA系统视图如何获取表的主外键信息
现在我们已经知道那些列是主键那些是外键,接下来的就是外键列所关联的外检表信息,这里需要用到[information_schema].[referential_constraints]系统视图,运行该视图如下:
探讨SQL利用INFORMATION_SCHEMA系统视图如何获取表的主外键信息
所以修改我们的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

运行结果如图:
探讨SQL利用INFORMATION_SCHEMA系统视图如何获取表的主外键信息
我们的查询结果中显示了太多的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


运行结果如图:
探讨SQL利用INFORMATION_SCHEMA系统视图如何获取表的主外键信息
有不对的地方还请大家拍砖!