How do I return the SQL data types from my query?

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

​x
 
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

 
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.

Answer

select * from information_schema.columns

 
select * from information_schema.columns​

could get you started.

Advertisement

Answer