探讨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'
运行结果如下:
我们都知道我们在定义列的时候一般的使用都是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
运行结果如图:
有不对的地方还请大家拍砖!
首先我们需要查询列的信息,这需要用到[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
运行结果如图:
有不对的地方还请大家拍砖!