SQL点滴4—筛选数据列的类型，字段大小，是否可为空，是否是主键，约束等等信息

 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