Display columns information

,

This procedure will return useful columns information for VB developers.
It shows column name, data type, size, vbtype, checktable (refferenced table) and flags: NoNull, IsIdentity, IsPK.
Calling sample: mysp_columns 'customers'

CREATE Proc mysp_columns (@tableName varchar(50))      
AS     
      
 IF exists (select * from dbo.sysobjects where id = object_id(N'[dbo].['+@tableName+']') and OBJECTPROPERTY(id, N'IsUserTable') =1)     
 BEGIN     
  select     
   a.name Name,   
      
   b.name DataType,   
      
   case b.name   
       when 'nchar' then a.length/2   
       when 'nvarchar' then a.length/2   
       else a.length   
   end [Size] ,   
      
   case b.name   
       when 'bit' then 'Boolean'   
       when 'char' then 'String'   
       when 'datetime' then 'Date'   
       when 'decimal' then 'String'   
       when 'float' then 'Double'   
       when 'int' then 'Long'   
       when 'money' then 'Currency'   
       when 'nchar' then 'String'   
       when 'ntext' then 'String'   
       when 'numeric' then 'String'   
       when 'nvarchar' then 'String'   
       when 'real' then 'Single'   
       when 'smalldatetime' then 'Date'   
       when 'smallint' then 'Integer'   
       when 'smallmoney' then 'Currency'   
       when 'text' then 'String'   
       when 'tinyint' then 'Byte'   
           when 'varchar' then 'String'   
       else 'Variant'   
   end VBType ,   
      
   allownulls NoNull ,   
      
   a.status/128 IsIdentity,   
      
   (select 1   
     from  
     sysobjects c_obj, sysobjects t_obj, syscolumns col,  
         master.dbo.spt_values v, sysindexes i 
     where   
           c_obj.uid = user_id()  
     AND c_obj.xtype  = 'PK'  
     AND t_obj.id = c_obj.parent_obj  
     AND t_obj.xtype = 'U'  
     AND t_obj.id = col.id  
     AND col.name = index_col(t_obj.name, i.indid,v.number)  
     AND t_obj.id = i.id  
     AND c_obj.name = i.name  
     AND v.number > 0  
     AND v.number <= i.keycnt  
     AND v.type = 'P' 
     AND t_obj.name = @tableName 
         AND col.name = a.name 
   )  IsPK,   
      
   (SELECT  TOP 1 object_name(ref.rkeyid)   
      FROM   
     sysobjects c_obj, sysobjects t_obj, syscolumns col,  
     sysreferences ref 
    WHERE   
     c_obj.uid = user_id()  
     AND c_obj.xtype IN ('F ')  
     AND t_obj.id = c_obj.parent_obj AND t_obj.id = col.id  
     AND col.colid IN (ref.fkey1, ref.fkey2, ref.fkey3, ref.fkey4, ref.fkey5, ref.fkey6, ref.fkey7, ref.fkey8, ref.fkey9, ref.fkey10, ref.fkey11, ref.fkey12, ref.fkey13, ref.fkey14, ref.fkey15, ref.fkey16)  
     AND c_obj.id = ref.constid 
     AND t_obj.name = @TableName 
     AND col.name = a.name 
   ) [CheckTable]   
      
  from     
   syscolumns a join systypes b on a.xusertype=b.xusertype    
  where     
   id=object_id(@tableName) order by a.colorder   
 END     
      
 else     
  Print 'ERROR: No table named '+@tableName + ' found in ' + db_name() + ' database.'

Rate

Share

Share

Rate