项目需要将Access数据库中的数据导入到SQL Server中，需要检验导入后的数据完整性，数据值是否正确。我们使用的是Microsoft SQL Server 2008 Migration Assistant for Access这个工具，次工具专门用来将Access中的数据库导出到SQL Server中，我们的疑虑是这个导出过程中会不会因为认为的原因导致数据错误或者数据之间的关联丢失，看起来有点多次一举，但是还是找方法来做测试。于是就产生了今天的问题，怎么从SQL Server中找出所有的数据列的类型，字段大小，是否可为空，是否是主键，约束等等信息。我找很多资料鼓捣出这个存储过程，先来看看代码：
代码
1
USE
[
MIS
]
2 GO
3
4 /* ***** Object: StoredProcedure [dbo].[sp_SelectColumnInfor] Script Date: 09/23/2010 19:00:28 ***** */
5 SET ANSI_NULLS ON
6 GO
7
8 SET QUOTED_IDENTIFIER ON
9 GO
10
11 create procedure [ dbo ] . [ sp_SelectColumnInfor ]
12 as
13 declare @table_name varchar ( 250 )
14 -- create a temp table
15 create table #tempTable(
16 TABLE_NAME nvarchar ( 128 ),
17 COLUMN_NAME nvarchar ( 128 ),
18 IS_NULLABLE varchar ( 3 ),
19 DATA_TYPE nvarchar ( 128 ),
20 CHARACTER_MAXIMUM_LENGTH int ,
21 CONSTRAINT_NAME nvarchar ( 128 ),
22 )
23 -- create a cursor
24 declare curTABLE cursor for
25 select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = ' BASE TABLE '
26 for read only
27
28 open curTABLE
29 fetch next from curTABLE into @table_name
30 while @@FETCH_STATUS = 0
31 begin
32 insert into #tempTable
33 select sc. [ TABLE_NAME ] ,sc. [ COLUMN_NAME ] ,sc. [ IS_NULLABLE ] ,sc. [ DATA_TYPE ] ,sc. [ CHARACTER_MAXIMUM_LENGTH ]
34 ,scc.CONSTRAINT_NAME
35 from INFORMATION_SCHEMA.COLUMNS sc
36 left join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE scc on sc.COLUMN_NAME = scc.COLUMN_NAME and sc.TABLE_NAME = scc.TABLE_NAME
37 where sc. [ TABLE_NAME ] = @table_name -- order by TABLE_NAME,COLUMN_NAME
38
39 fetch next from curTABLE into @table_name
40 end
41 close curTABLE
42 deallocate curTABLE
43
44 select * from #tempTable order by TABLE_NAME,COLUMN_NAME
45 drop table #tempTable
46 GO
2 GO
3
4 /* ***** Object: StoredProcedure [dbo].[sp_SelectColumnInfor] Script Date: 09/23/2010 19:00:28 ***** */
5 SET ANSI_NULLS ON
6 GO
7
8 SET QUOTED_IDENTIFIER ON
9 GO
10
11 create procedure [ dbo ] . [ sp_SelectColumnInfor ]
12 as
13 declare @table_name varchar ( 250 )
14 -- create a temp table
15 create table #tempTable(
16 TABLE_NAME nvarchar ( 128 ),
17 COLUMN_NAME nvarchar ( 128 ),
18 IS_NULLABLE varchar ( 3 ),
19 DATA_TYPE nvarchar ( 128 ),
20 CHARACTER_MAXIMUM_LENGTH int ,
21 CONSTRAINT_NAME nvarchar ( 128 ),
22 )
23 -- create a cursor
24 declare curTABLE cursor for
25 select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = ' BASE TABLE '
26 for read only
27
28 open curTABLE
29 fetch next from curTABLE into @table_name
30 while @@FETCH_STATUS = 0
31 begin
32 insert into #tempTable
33 select sc. [ TABLE_NAME ] ,sc. [ COLUMN_NAME ] ,sc. [ IS_NULLABLE ] ,sc. [ DATA_TYPE ] ,sc. [ CHARACTER_MAXIMUM_LENGTH ]
34 ,scc.CONSTRAINT_NAME
35 from INFORMATION_SCHEMA.COLUMNS sc
36 left join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE scc on sc.COLUMN_NAME = scc.COLUMN_NAME and sc.TABLE_NAME = scc.TABLE_NAME
37 where sc. [ TABLE_NAME ] = @table_name -- order by TABLE_NAME,COLUMN_NAME
38
39 fetch next from curTABLE into @table_name
40 end
41 close curTABLE
42 deallocate curTABLE
43
44 select * from #tempTable order by TABLE_NAME,COLUMN_NAME
45 drop table #tempTable
46 GO
其实很简单的，只要查查INFORMATION_SCHEMA.COLUMNS , INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE这两个系统视图的功能就能明白。来看看执行这个存储过程得到的结果：
下次介绍Microsoft SQL Server 2008 Migration Assistant for Access这个工具的用法。
作者：Tyler Ning
出处：http://www.cnblogs.com/tylerdonet/
本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，如有问题，可以通过以下邮箱地址williamningdong@gmail.com 联系我，非常感谢。
