Suppressing duplicate rows

[Monisha]

Hi,

I need to get a list of columns and corresponding datatypes for the
given table name. I am using the query:

select
a.name,
b.type_name
from
syscolumns a,
master.dbo.spt_datatype_info b,
systypes c,
sysobjects d
where
a.xusertype = c.xusertype and
b.ss_dtype = c.xtype and
a.ID = (SELECT OBJECT_ID(<tablename>)) and
a.ID = d.ID and
d.ID = (SELECT OBJECT_ID(<tablename>))

This returns multiple sets of data for each column. This is because
the same column name is repeated in multiple tables in primary
key-foreign key relationships. Is there a way in which I can get only
one row for each column of a table?

[Simon Hayes]

"Monisha" <mo*****@ggn.hcltech.com> wrote in message
news:91*************************@posting.google.co m...

Hi,

I need to get a list of columns and corresponding datatypes for the
given table name. I am using the query:

select
a.name,
b.type_name
from
syscolumns a,
master.dbo.spt_datatype_info b,
systypes c,
sysobjects d
where
a.xusertype = c.xusertype and
b.ss_dtype = c.xtype and
a.ID = (SELECT OBJECT_ID(<tablename>)) and
a.ID = d.ID and
d.ID = (SELECT OBJECT_ID(<tablename>))

This returns multiple sets of data for each column. This is because
the same column name is repeated in multiple tables in primary
key-foreign key relationships. Is there a way in which I can get only
one row for each column of a table?

Assuming you have SQL7 or greater, then using the INFORMATION_SCHEMA views
is often easier (and they're documented, unlike spt_datatype_info):

use pubs
go

select COLUMN_NAME, DATA_TYPE
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'titles'
go

The data type lengths etc are also available through that view, if you need
them.

Simon

[Erland Sommarskog]

Monisha (mo*****@ggn.hcltech.com) writes:

I need to get a list of columns and corresponding datatypes for the
given table name. I am using the query:

Involving the completely undocumented spt tables in a query is not
recommendable.

Here is a query that works with out spt_datatype_info:

select c.name, t.name
from syscolumns c
join systypes t on c.xtype = t.xtype
where c.id = object_id('accountstats')
and t.xusertype <= 255
order by c.colid

I should not that the cut-off value of 255 is not documented, but
something I concluded by inspecting systypes table in a database
that I have.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp