How to create column names/descriptors programmatically

Question

In SQL Server given a Table/View how can you generate a definition of the Table/View in the form:

C1 int,
C2 varchar(20),
C3 double

The information required to do it is contained in the meta-tables of SQL Server but is there a standard script / IDE faciltity to output the data contained there in the form described above ?.

For the curious I want this as I have to maintain a number of SP's which contain Table objects (that is a form of temporary table used by SQL Server). The Table objects need to match the definition of Tables or Views already in the database - it would make life a lot easier if these definitions could be generated automatically.

Answer 1

If you want to duplpicate a table definition you could use:

select top 0
  *
into
  newtable
from
  mytable

Edit: Sorry, just re-read your question, and realised this might not answer it. Could you be clear on what you are after, do you want an exact duplicate of the table definition, or a table that contains information about the tables definition?

Answer 2

Here is an example of listing the names and types of columns in a table:

select 
 COLUMN_NAME, 
 COLUMN_DEFAULT, 
 IS_NULLABLE, 
 DATA_TYPE, 
 CHARACTER_MAXIMUM_LENGTH, 
 NUMERIC_PRECISION, 
 NUMERIC_SCALE
from 
 INFORMATION_SCHEMA.COLUMNS
where 
 TABLE_NAME = 'YOUR_TABLE_NAME_HERE' 
order by 
 Ordinal_Position

Generating DDL from that information is more difficult. There seems to be some suggestions at SQLTeam

Answer 3

Thanks for your replies. Yes I do want an exact duplicate of the DDL but I've realised I misstated exactly what I needed. It's DDL which will create a temporary table which will match the columns of a view.

I realised this in looking at Duckworths suggestion - which is good but unfortunately doesn't cover the case of a view.

SELECT VIEWDEFINITION FROM INFORMATIONSCHEMA.VIEWS

... will give you a list of columns in a view and (assuming that all columns in the view are derived directly from a table) it should then be possible to use an amended version of Duckworths suggestion to pull together the relevant DLL.

I'm just amazed it's not easier ! I was expecting someone to tell me that there was a well established routine to do this given the TABLE objects need to have all columns full defined (rather than the way Oracle does it which is to say - "give me something which looks like table X".

Anyway thanks again for help and any further suggestions welcomed.

Answer 4

In this posting to another question I've got a DB reverse engineering script that will do tables, views, PK, UK and index definitions and foreign keys. This one is for SQL Server 2005 and is a port of one I originally wrote for SQL Server 2000. If you need a SQL Server 2000 version add a comment to this post and I'll post it up here.