Tuesday, July 27, 2010

function for inspecting column data type and length

Run this tsql to create a function to which you can pass the table and column names as parameters. It will return the data type, length of the column specified
declare @sql varchar(1000) 
 if object_id('fn_GetColumnMetaData') is null 
 Begin
  print '    Creating function dbo.fn_GetColumnMetaData'
  set @sql = 'create function dbo.fn_GetColumnMetaData(@tblName varchar(100), @colName varchar(100))
   Returns Table
   as Return (
    select length=case when t.name=''nvarchar'' then c.length/2 else c.length end, maxLength=t.length, typeName=t.name
    from syscolumns c join systypes t on  c.xtype=t.xtype and t.name != ''sysname''
    where id=object_Id(@tblName) and c.name=@colName
   )'  
  exec(@sql)
 End 

No comments: