How to get the "CREATE TABLE" query?

Question

When I right-click on my view and click on "Script View As", I get the following error:

Property TextHeader is not available for View '[dbo].[TableName]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. The text is encrypted. (Microsoft.SqlServer.Smo)

I was able to use bcp to get a dump of the table and also create a format file as given here. The table has about 60 columns and I do not want to manually write the CREATE TABLE query. Is there a way to do this automatically?

I was hoping that

BULK INSERT DB.dbo.TableName
FROM 'E:\Databases\TableName'
WITH (FORMATFILE = 'E:\Databases\TableName.Fmt');
GO

would do the trick but it looks like the table itself should be present in the database before I can execute the above query. Any suggestions?

As the error suggests, the problem is probably permissions. Can you access the INFORMATION_SCHEMA tables? You can construct the table from there (at least the columns, which is all that you need for the bcp). — Gordon Linoff, Aug 10 '12 at 00:48
@GordonLinoff: If this is the one under `System.Views`, yes, I am able to access them. So the only way is to perhaps get a dump of this schema and then manually create the query? — Legend, Aug 10 '12 at 00:52

score 2 · Accepted Answer · answered Aug 10 '12 at 01:01

You can construct the create table statement from INFORMATION_SCHEMA.Columns. Something like:

select (column_name + ' ' + data_type +
        (case when character_maximum_length is not null
              then '('+character_maximum_length+')'
              else ''
         end) + ','
       ) as ColumnDef
from Information_Schema.columns
order by ordinal_position

This is probably good enough. You can make it more complicated if you have to deal with numerics, for instance, or want "is null" to be accurate.

Copy the results into a new window, add the create table statement, remove the final comma and add the final closing paren.

You can do all the last step in a more complex SQL statement, but it is easier to do manually for a one-time effort.

How to get the "CREATE TABLE" query?

1 Answers1