CODEWITHSUNDEEP
sqlsql-server-2008bcp
Legend
Legend

Reputation: 116850

How to get the "CREATE TABLE" query?

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?

Upvotes: 0

Views: 1757

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

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.

Upvotes: 2

Related Questions