Wednesday, March 21, 2012

find type of a column

hi

is it possible to find a column's type inside stored procedure? I am going to get the name of the table and then work with the columns and I just need the type of the column if I have had the name of it

regards

Maybe something like this:

use tempdb
go

create table ##typeExample (anotherType integer, targetType double precision)

declare @.objectName sysname set @.objectName = '##typeExample'
declare @.columnName sysname set @.columnName = 'targetType'

select c.colid,
c.[name] as columnName,
t.[name] as type
from sysobjects o
inner join syscolumns c
on o.id = c.id
and o.[name] = @.objectName
and c.[name] = @.columnName
-- and o.type = 'U' -- Uncomment if you want only user tables
-- and o.type in ('U','V') -- Uncomment if you want tables and views
inner join systypes t
on c.xtype = t.xtype

-- - Output: -

-- colid columnName type
-- -
-- 2 targetType float

go

drop table ##typeExample
go


Dave

No comments:

Post a Comment