SQL点滴之筛选数据列的信息

前面两次点滴分享中，笔者与我们分享了SET QUOTED_IDENTIFIER 的作用，并给我们介绍了一个简单的字符串分割函数。这一次，笔者为我们介绍怎么从SQL Server中找出所有的数据列的类型，字段大小，是否可为空，是否是主键，约束等等信息。

项目需要将Access数据库中的数据导入到SQL Server中，需要检验导入后的数据完整性，数据值是否正确。我们使用的是Microsoft SQL Server 2008 Migration Assistant for Access这个工具，次工具专门用来将Access中的数据库导出到SQL Server中，我们的疑虑是这个导出过程中会不会因为认为的原因导致数据错误或者数据之间的关联丢失，看起来有点多次一举，但是还是找方法来做测试。于是就产生了今天的问题，怎么从SQL Server中找出所有的数据列的类型，字段大小，是否可为空，是否是主键，约束等等信息。我找很多资料鼓捣出这个存储过程，先来看看代码：

USE [MIS]  
GO  
 
/****** Object:  StoredProcedure [dbo].[sp_SelectColumnInfor]    Script Date: 09/23/2010 19:00:28 ******/  
SET ANSI_NULLS ON 
GO  
 
SET QUOTED_IDENTIFIER ON 
GO  
 
create procedure [dbo].[sp_SelectColumnInfor]  
as 
declare @table_name varchar(250)  
--create a temp table  
create table #tempTable(  
TABLE_NAME nvarchar(128),  
COLUMN_NAME nvarchar(128),  
IS_NULLABLE varchar(3),  
DATA_TYPE nvarchar(128),  
CHARACTER_MAXIMUM_LENGTH int,  
CONSTRAINT_NAME nvarchar(128),  
)   
--create a cursor  
declare curTABLE cursor for 
select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE='BASE TABLE' 
for read only 
 
open curTABLE  
fetch next from curTABLE into @table_name  
while @@FETCH_STATUS =0  
begin 
insert into #tempTable  
select sc.[TABLE_NAME],sc.[COLUMN_NAME],sc.[IS_NULLABLE],sc.[DATA_TYPE],sc.[CHARACTER_MAXIMUM_LENGTH]  
,scc.CONSTRAINT_NAME  
from INFORMATION_SCHEMA.COLUMNS sc   
left join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE scc on sc.COLUMN_NAME=scc.COLUMN_NAME and sc.TABLE_NAME=scc.TABLE_NAME  
where sc.[TABLE_NAME]=@table_name --order by TABLE_NAME,COLUMN_NAME  
 
fetch next from curTABLE into @table_name  
end 
close curTABLE  
deallocate curTABLE  
select * from #tempTable order by TABLE_NAME,COLUMN_NAME  
drop table #tempTable  
GO