SQL bit-by-bit filtering data column information

In the previous two slides, I shared with us the role of SET QUOTED_IDENTIFIER and introduced a simple string segmentation function. This time, I will introduce how to find out the types, field sizes, whether it can be blank, whether it is a primary key, constraints, and other information of all data columns from SQL Server.

The project needs to import data from the Access database to SQL Server. It needs to check the integrity of the imported data and whether the data value is correct. We use Microsoft SQL Server 2008 Migration Assistant for Access, which is used to export the database in Access to SQL Server, our doubt is whether the export process will result in data errors or lost associations between data due to the reasons we think. It seems that there are a few attempts, but we still need to find a method for testing. As a result, today's problem arises: how to find out the types, field sizes, whether it can be blank, whether it is a primary key, constraints, and other information of all data columns from SQL Server. I am looking for a lot of information to drum up this stored procedure. Let's take a look at the Code:

 

  
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 
 

In fact, it is very easy to check the functions of the INFORMATION_SCHEMA.COLUMNS and INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE system views. To see the results of executing this stored procedure:

 

Next, we will introduce Microsoft SQL Server 2008 Migration Assistant for Access.