Como obtener todos los campos de una tabla en SQL 2000 y en 2005

--como obtener todos los campos de una tabla
Una vez determinado el object id, en este caso el 146230095 que corresponde a la tabla store de adventureworks, podremos ejecutar una de las siguientes consultas dependiendo de si estamos en 2000 o en 2005 (y 2008)

--sql server 2000
select syscolumns.name [name], systypes.name [type],
syscolumns.length as 'length',
syscolumns.isnullable as 'isnullable'
from syscolumns inner join systypes
on syscolumns.xtype = systypes.xtype and
syscolumns.xusertype = systypes.xusertype
where syscolumns.id = 14623095"
and systypes.name <> 'sysname'
order by syscolumns.colid

--sql server 2005 & 2008

with ctabla
as
(select s.name + '.' + t.name tabla, t.object_id oid from sys.tables t
inner join sys.schemas s on t.schema_id = s.schema_id)

select c.name [name],
(select top 1name from sys.systypes where xtype = c.system_type_id) [type],
c.max_length [length], c.is_nullable [isnullable] from sys.columns c
inner join ctabla t on c.object_id = t.oid
where t.oid = 14623095
order by c.name

Hugo Román Bernachea
Mail de contacto: SQLServer777@gmail.com

Microsoft Certified DBA
Microsoft Certified Trainer

http://www.linkedin.com/in/bernachea

Twitter: @bernachea