Select all data types 2

[Ovatvvon]

What is the easiest way to find all numeric and decimal data type columns in a database?

I'm presuming I'd select from sysobjects, but not sure if I should select on xtype, or another column, and also what specific column value I should search for to find the data types I want to find.

Any ideas?

-Ovatvvon :-Q

 

[mrdenny]

For SQL 2000 look in the dbo.syscolumns table.
For SQL 2005 look in the sys.columns DMV.

You can find the type info in the systypes table or sys.types DMV.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[Ovatvvon]

Ok, that seems to work, except I can have several columns with the same name and data type, yet in separate tables. I don't see a parent_object_id or column like that...

How do I determine what table each column is in?

btw, this is in SQL 2005, so I'm using the following query:

select *
from sys.columns
where system_type_id = 106
ORDER BY name

-Ovatvvon :-Q

 

[RiverGuy]

object_id is the id of the parent object.

An alternative would be to select from INFORMATION_SCHEMA.COLUMNS. You'll get the table name and column name in the one table.

 

[Ovatvvon]

I just realized that; thanks for clarifying!

-Ovatvvon :-Q

 

[mrdenny]

To pull the object name use OBJECT_NAME(parent_object_id) as a column.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog