views:

170

answers:

3

I've a SQL query that queries an enormous (as in, hundreds of views/tables with hard-to-read names like CMM-CPP-FAP-ADD) database that I don't need nor want to understand. The result of this query needs to be stored in a staging table to feed a report.

I need to create the staging table, but with hundreds of views/tables to dig through to find the data types that are being represented here, I have to wonder if there's a better way to construct this table.

Can anyone advise how I would use any of the SQL Server 2008 tools to divine the source data types in my SQL 2000 database?

As a general example, I want to know from a query like:

SELECT Auth_First_Name, Auth_Last_Name, Auth_Favorite_Number 
FROM Authors

Instead of the actual results, I want to know that:

Auth_First_Name is char(25)
Auth_Last_Name is char(50)
Auth_Favorite_Number is int

I'm not interested in constraints, I really just want to know the data types.

+2  A: 
select * from information_schema.columns

could get you started.

erikkallen
Invalid object name 'information_schema.columns'
JMP
Do you have a CASE SENSITIVE database? If so, you must use `SELECT * FROM INFORMATION_SCHEMA.Columns`
Raj More
Yes, it is case sensitive. I tried SELECT * FROM INFORMATION_SCHEMA.Columns and get the same invalid object error. This is SQL Server 2000 (8.0.2055), does INFORMATION_SCHEMA exist in this version?
JMP
ok nvm, I tried SELECT * FROM [databasename].INFORMATION_SCHEMA.COLUNMS and I'm getting somewhere. Thanks for being patient with me on this!
JMP
+1  A: 

Can you get away with recreating the staging table from scratch every time the query is executed? If so you could use SELECT ... INTO syntax and let SQL Server worry about creating the table using the correct column types etc.

SELECT *
INTO your_staging_table
FROM enormous_collection_of_views_tables_etc
LukeH
Unfortunately not an option.
JMP
He could also create that staging table just to get the columns data types, using the information_schema table pointed by erikkallen.
Spidey
@JM: How about running the `SELECT ... INTO ... FROM ... WHERE 1=0` syntax as a one-off to create a dummy table, and then use Management Studio to script the table creation SQL for the dummy table?
LukeH
+1 That's a really fascinating suggestion. I kind of like it, although I think the dinosaurs around here wouldn't be to hot for it.
JMP
A: 

You can also use...

SQL_VARIANT_PROPERTY()

...in cases where you don't have direct access to the metadata (e.g. a linked server query perhaps?).

http://msdn.microsoft.com/en-us/library/ms178550.aspx

In SQL Server 2005 and beyond you are better off using the catalog views (sys.columns) as opposed to INFORMATION_SCHEMA. Unless portability to other platforms is important. Just keep in mind that the INFORMATION_SCHEMA views won't change and so they will progressively be lacking information on new features etc. in successive versions of SQL Server.

Aaron Bertrand