Column Data Type

Hello, I would like to get the columns in a table, this works fine:
SELECT syscolumns.*
FROM sysobjects
INNER JOIN syscolumns ON sysobjects.id = syscolumns.id
WHERE sysobjects.name = 'Customers'
ORDER BY syscolumns.colid
However, is there a way to get Column_Data_Type as for example "nchar" or "varchar" instead of having it as numbers.
Can anybody help ?
thanksYou can join to systypes using the xtype column.
But you should probably, instead, use the INFORMATION_SCHEMAviews. Directly accessing the system tables is not recommended,especially as we get closer to 2005 -- those tables are beingdeprecated. Try:
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo'
|||SELECT syscolumns.* , systypes.name
FROM sysobjects
INNER JOIN syscolumns ON sysobjects.id = syscolumns.id
inner join systypes on syscolumns.xusertype = systypes.xusertype
WHERE sysobjects.name = 'Customers'
ORDER BY syscolumns.colid|||

That was a great query in fact.
I am doing something similar, I would like to get all SP, View, triggers, is that possible?
Regards

|||--P=SP, V=Views, TR=Triggers
select *
from sysobjects
where xtype IN ('P', 'V', 'TR')
order by xtype
If you need the information from all databases, you can use the master DB and select from sysdatabases. Run a cursor and put the data into a table so you can access it after the cursor completes.
Nick|||

I will give it a try later. That is great.
Tell me, where do you get such information? Are they present in somewhere? I usually use SP, View, Triggers, but never went into details of such queries, I guess they are system queries right?
regards

|||

Here is a link to all the system tables and what they are for:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sys_00_690z.asp

Here is a really helpful tool that shows the links between all the tables.

http://www.microsoft.com/sql/techinfo/productdoc/2000/systables.asp

Nick

|||Those were extremly great links. Thanks a lot.
Regards