views:

4786

answers:

11

I've spent a good amount of time coming up with solution to this problem, so in the spirit of this post, I'm posting it here, since I think it might be useful to others.

If anyone has a better script, or anything to add, please post it.

Edit: Yes guys, I know how to do it in Management Studio - but I needed to be able to do it from within another application.

+9  A: 

Here's the script that I came up with. It handles Identity columns, default values, and primary keys. It does not handle foreign keys, indexes, triggers, or any other clever stuff. It works on SQLServer 2000, 2005 and 2008.

declare @table varchar(100)
set @table = 'MyTable' -- set table name here
declare @sql table(s varchar(1000), id int identity)

-- create statement
insert into  @sql(s) values ('create table [' + @table + '] (')

-- column list
insert into @sql(s)
select 
    '  ['+column_name+'] ' + 
    data_type + coalesce('('+cast(character_maximum_length as varchar)+')','') + ' ' +
    case when exists ( 
        select id from syscolumns
        where object_name(id)[email protected]
        and name=column_name
        and columnproperty(id,name,'IsIdentity') = 1 
    ) then
        'IDENTITY(' + 
        cast(ident_seed(@table) as varchar) + ',' + 
        cast(ident_incr(@table) as varchar) + ')'
    else ''
    end + ' ' +
    ( case when IS_NULLABLE = 'No' then 'NOT ' else '' end ) + 'NULL ' + 
    coalesce('DEFAULT '+COLUMN_DEFAULT,'') + ','

 from information_schema.columns where table_name = @table
 order by ordinal_position

-- primary key
declare @pkname varchar(100)
select @pkname = constraint_name from information_schema.table_constraints
where table_name = @table and constraint_type='PRIMARY KEY'

if ( @pkname is not null ) begin
    insert into @sql(s) values('  PRIMARY KEY (')
    insert into @sql(s)
     select '   ['+COLUMN_NAME+'],' from information_schema.key_column_usage
     where constraint_name = @pkname
     order by ordinal_position
    -- remove trailing comma
    update @sql set s=left(s,len(s)-1) where [email protected]@identity
    insert into @sql(s) values ('  )')
end
else begin
    -- remove trailing comma
    update @sql set s=left(s,len(s)-1) where [email protected]@identity
end

-- closing bracket
insert into @sql(s) values( ')' )

-- result!
select s from @sql order by id
Blorgbeard
Lots of systems won't be able to run this due to the dynamic sql. On your local box probably not a problem, but just wanted to point that out.
Jason Short
What dynamic sql? This script generates sql - it doesn't execute it.
Blorgbeard
+1  A: 

Very pretty, but something of a waste of "a good amount of time" unless there was some other purpose to the exercise, wouldn't you say?

I remember doing something similar for Oracle versions 5 and 6 in the lates 80s and early 90s, but since then it's been almost entirely unnecessary.

For MSSQL I load up the (free) SQL Server Management Studio Express, locate my table in the tree view and right-click it, selecting "Script Table As..." and Bingo!

I'm not meaning to rain on your parade, you've done a nice piece of work there, but mostly what you've achieved is an understanding of how SQL Server stores database definition information, which is not of great value unless, perhaps, you're planning on writing a report generator, an ORM or perhaps intend becoming a DBA?

(sits back and waits for down-votes)

Mike Woodhouse
A: 

Can't you right click the table in Management Studio and select "Script Table As Create"?

Shaun Austin
A: 

Very nice. But what's the point?

Ian Nelson
+2  A: 

Credit due to @Blorgbeard for sharing his script. I'll certainly bookmark it in case I need it.

Yes, you can "right click" on the table and script the CREATE TABLE script, but:

  • The a script will contain loads of cruft (interested in the extended properties anyone?)
  • If you have 200+ tables in your schema, it's going to take you half a day to script the lot by hand.

With this script converted into a stored procedure, and combined with a wrapper script you would have a nice automated way to dump your table design into source control etc.

The rest of your DB code (SP's, FK indexes, Triggers etc) would be under source control anyway ;)

Guy
+1  A: 

If you have 200+ tables in your schema, it's going to take you half a day to script the lot by hand.

Or "Script Database As..."

The thing is, you should already have all your table CREATE scripts in source control if you cook 'em by hand. Or use ActiveRecord migrations if you have ruby, which do the same thing only nicer. If you used the management studio to create the tables, you may already have set stuff you didn't ask for.

And anyway, if STATISTICS_NORECOMPUTE changes, it ought to be recorded, oughtn't it?

Mike Woodhouse
+1  A: 

Something I've noticed - in the INFORMATION_SCHEMA.COLUMNS view, CHARACTER_MAXIMUM_LENGTH gives a size of 2147483647 (2^31-1) for field types such as image and text. ntext is 2^30-1 (being double-byte unicode and all).

This size is included in the output from this query, but it is invalid for these data types in a CREATE statement (they should not have a maximum size value at all). So unless the results from this are manually corrected, the CREATE script won't work given these data types.

I imagine it's possible to fix the script to account for this, but that's beyond my SQL capabilities.

Jivlain
A: 

If you are using management studio and have the query analyzer window open you can drag the table name to the query analyzer window and ... bingo! you get the table script. I've not tried this in SQL2008

That's a neat shortcut, but again: I needed to do it programmatically.
Blorgbeard
+4  A: 

I've modified the version above to run for all tables and support new SQL 2005 data types. It also retains the primary key names. Works only on SQL 2005 (using cross apply).


select  'create table [' + so.name + '] (' + o.list + ')' + CASE WHEN tc.Constraint_Name IS NULL THEN '' ELSE 'ALTER TABLE ' + so.Name + ' ADD CONSTRAINT ' + tc.Constraint_Name  + ' PRIMARY KEY ' + ' (' + LEFT(j.List, Len(j.List)-1) + ')' END
from    sysobjects so
cross apply
    (SELECT 
        '  ['+column_name+'] ' + 
        data_type + case data_type
      when 'sql_variant' then ''
      when 'text' then ''
      when 'decimal' then '(' + cast(numeric_precision_radix as varchar) + ', ' + cast(numeric_scale as varchar) + ')'
      else coalesce('('+case when character_maximum_length = -1 then 'MAX' else cast(character_maximum_length as varchar) end +')','') end + ' ' +
        case when exists ( 
     select id from syscolumns
     where object_name(id)=so.name
     and name=column_name
     and columnproperty(id,name,'IsIdentity') = 1 
        ) then
     'IDENTITY(' + 
     cast(ident_seed(so.name) as varchar) + ',' + 
     cast(ident_incr(so.name) as varchar) + ')'
        else ''
        end + ' ' +
         (case when IS_NULLABLE = 'No' then 'NOT ' else '' end ) + 'NULL ' + 
          case when information_schema.columns.COLUMN_DEFAULT IS NOT NULL THEN 'DEFAULT '+ information_schema.columns.COLUMN_DEFAULT ELSE '' END + ', ' 

     from information_schema.columns where table_name = so.name
     order by ordinal_position
    FOR XML PATH('')) o (list)
left join
    information_schema.table_constraints tc
on  tc.Table_name  = so.Name
AND tc.Constraint_Type = 'PRIMARY KEY'
cross apply
    (select '[' + Column_Name + '], '
     FROM information_schema.key_column_usage kcu
     WHERE kcu.Constraint_Name = tc.Constraint_Name
     ORDER BY
     ORDINAL_POSITION
     FOR XML PATH('')) j (list)
where   xtype = 'U'
AND name NOT IN ('dtproperties')

This is fantastic! I've been looking for something like this for quite some time! (trying to script table structure into our version control)
Matt
Thank you for posting this! I think you should be using the numeric_precision column, not numeric_precision_radix, and in addition to the decimal datatype, it should work the same for the numeric datatype.
Scott Whitlock
+2  A: 

If the application you are generating the scripts from is a .NET application, you may want to look into using SMO (Sql Management Objects). Reference this SQL Team link on how to use SMO to script objects.

A: 

-- or you could create a stored procedure ... first with Id creation USE [db] GO

/****** Object:  StoredProcedure [dbo].[procUtils_InsertGeneratorWithId]    Script Date: 06/13/2009 22:18:11 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


create PROC [dbo].[procUtils_InsertGeneratorWithId]    
(    
@domain_user varchar(50),    
@tableName varchar(100)    
)     


as    

--Declare a cursor to retrieve column specific information for the specified table    
DECLARE cursCol CURSOR FAST_FORWARD FOR     
SELECT column_name,data_type FROM information_schema.columns WHERE table_name = @tableName    
OPEN cursCol    
DECLARE @string nvarchar(3000) --for storing the first half of INSERT statement    
DECLARE @stringData nvarchar(3000) --for storing the data (VALUES) related statement    
DECLARE @dataType nvarchar(1000) --data types returned for respective columns    
DECLARE @IDENTITY_STRING nvarchar ( 100 )    
SET @IDENTITY_STRING = ' '     
select  @IDENTITY_STRING    
SET @string='INSERT '[email protected]+'('    
SET @stringData=''    

DECLARE @colName nvarchar(50)    

FETCH NEXT FROM cursCol INTO @colName,@dataType    

IF @@fetch_status<>0    
 begin    
 print 'Table '[email protected]+' not found, processing skipped.'    
 close curscol    
 deallocate curscol    
 return    
END    

WHILE @@FETCH_STATUS=0    
BEGIN    
IF @dataType in ('varchar','char','nchar','nvarchar')    
BEGIN    
 --SET @[email protected]+'''''''''+isnull('[email protected]+','''')+'''''',''+'    
 SET @[email protected]+''''+'''+isnull('''''+'''''+'[email protected]+'+'''''+''''',''NULL'')+'',''+'    
END    
ELSE    
if @dataType in ('text','ntext') --if the datatype is text or something else     
BEGIN    
 SET @[email protected]+'''''''''+isnull(cast('[email protected]+' as varchar(2000)),'''')+'''''',''+'    
END    
ELSE    
IF @dataType = 'money' --because money doesn't get converted from varchar implicitly    
BEGIN    
 SET @[email protected]+'''convert(money,''''''+isnull(cast('[email protected]+' as varchar(200)),''0.0000'')+''''''),''+'    
END    
ELSE     
IF @dataType='datetime'    
BEGIN    
 --SET @[email protected]+'''convert(datetime,''''''+isnull(cast('[email protected]+' as varchar(200)),''0'')+''''''),''+'    
 --SELECT 'INSERT Authorizations(StatusDate) VALUES('+'convert(datetime,'+isnull(''''+convert(varchar(200),StatusDate,121)+'''','NULL')+',121),)' FROM Authorizations    
 --SET @[email protected]+'''convert(money,''''''+isnull(cast('[email protected]+' as varchar(200)),''0.0000'')+''''''),''+'    
 SET @[email protected]+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'[email protected]+',121)+'''''+''''',''NULL'')+'',121),''+'    
  --                             'convert(datetime,'+isnull(''''+convert(varchar(200),StatusDate,121)+'''','NULL')+',121),)' FROM Authorizations    
END    
ELSE     
IF @dataType='image'     
BEGIN    
 SET @[email protected]+'''''''''+isnull(cast(convert(varbinary,'[email protected]+') as varchar(6)),''0'')+'''''',''+'    
END    
ELSE --presuming the data type is int,bit,numeric,decimal     
BEGIN    
 --SET @[email protected]+'''''''''+isnull(cast('[email protected]+' as varchar(200)),''0'')+'''''',''+'    
 --SET @[email protected]+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'[email protected]+',121)+'''''+''''',''NULL'')+'',121),''+'    
 SET @[email protected]+''''+'''+isnull('''''+'''''+convert(varchar(200),'[email protected]+')+'''''+''''',''NULL'')+'',''+'    
END    

SET @[email protected][email protected]+','    

FETCH NEXT FROM cursCol INTO @colName,@dataType    
END    
DECLARE @Query nvarchar(4000)    

SET @query ='SELECT '''+substring(@string,0,len(@string)) + ') VALUES(''+ ' + substring(@stringData,0,len(@stringData)-2)+'''+'')'' FROM '[email protected]    
exec sp_executesql @query    
--select @query    

CLOSE cursCol    
DEALLOCATE cursCol    


  /*
USAGE

*/

GO

-- and second without iD INSERTION

USE [db]
GO

/****** Object:  StoredProcedure [dbo].[procUtils_InsertGenerator]    Script Date: 06/13/2009 22:20:52 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[procUtils_InsertGenerator]        
(        
@domain_user varchar(50),        
@tableName varchar(100)        
)         


as        

--Declare a cursor to retrieve column specific information for the specified table        
DECLARE cursCol CURSOR FAST_FORWARD FOR         


-- SELECT column_name,data_type FROM information_schema.columns WHERE table_name = @tableName        
/* NEW     
SELECT c.name , sc.data_type  FROM sys.extended_properties AS ep                   
INNER JOIN sys.tables AS t ON ep.major_id = t.object_id                   
INNER JOIN sys.columns AS c ON ep.major_id = c.object_id AND ep.minor_id                   
= c.column_id                   
INNER JOIN INFORMATION_SCHEMA.COLUMNS sc ON t.name = sc.table_name and                   
c.name = sc.column_name                   
WHERE t.name = @tableName and c.is_identity=0      
  */      

select object_name(c.object_id) "TABLE_NAME", c.name "COLUMN_NAME", s.name "DATA_TYPE"      
  from sys.columns c          
  join sys.systypes s on (s.xtype = c.system_type_id)          
  where object_name(c.object_id) in (select name from sys.tables where name not like 'sysdiagrams')          
   AND object_name(c.object_id) in (select name from sys.tables where [name][email protected]  ) and c.is_identity=0 and s.name not like 'sysname'  




OPEN cursCol        
DECLARE @string nvarchar(3000) --for storing the first half of INSERT statement        
DECLARE @stringData nvarchar(3000) --for storing the data (VALUES) related statement        
DECLARE @dataType nvarchar(1000) --data types returned for respective columns        
DECLARE @IDENTITY_STRING nvarchar ( 100 )        
SET @IDENTITY_STRING = ' '         
select  @IDENTITY_STRING        
SET @string='INSERT '[email protected]+'('        
SET @stringData=''        

DECLARE @colName nvarchar(50)        

FETCH NEXT FROM cursCol INTO @tableName , @colName,@dataType        

IF @@fetch_status<>0        
 begin        
 print 'Table '[email protected]+' not found, processing skipped.'        
 close curscol        
 deallocate curscol        
 return        
END        

WHILE @@FETCH_STATUS=0        
BEGIN        
IF @dataType in ('varchar','char','nchar','nvarchar')        
BEGIN        
 --SET @[email protected]+'''''''''+isnull('[email protected]+','''')+'''''',''+'        
 SET @[email protected]+''''+'''+isnull('''''+'''''+'[email protected]+'+'''''+''''',''NULL'')+'',''+'        
END        
ELSE        
if @dataType in ('text','ntext') --if the datatype is text or something else         
BEGIN        
 SET @[email protected]+'''''''''+isnull(cast('[email protected]+' as varchar(2000)),'''')+'''''',''+'        
END        
ELSE        
IF @dataType = 'money' --because money doesn't get converted from varchar implicitly        
BEGIN        
 SET @[email protected]+'''convert(money,''''''+isnull(cast('[email protected]+' as varchar(200)),''0.0000'')+''''''),''+'        
END        
ELSE         
IF @dataType='datetime'        
BEGIN        
 --SET @[email protected]+'''convert(datetime,''''''+isnull(cast('[email protected]+' as varchar(200)),''0'')+''''''),''+'        
 --SELECT 'INSERT Authorizations(StatusDate) VALUES('+'convert(datetime,'+isnull(''''+convert(varchar(200),StatusDate,121)+'''','NULL')+',121),)' FROM Authorizations        
 --SET @[email protected]+'''convert(money,''''''+isnull(cast('[email protected]+' as varchar(200)),''0.0000'')+''''''),''+'        
 SET @[email protected]+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'[email protected]+',121)+'''''+''''',''NULL'')+'',121),''+'        
  --                             'convert(datetime,'+isnull(''''+convert(varchar(200),StatusDate,121)+'''','NULL')+',121),)' FROM Authorizations        
END        
ELSE         
IF @dataType='image'         
BEGIN        
 SET @[email protected]+'''''''''+isnull(cast(convert(varbinary,'[email protected]+') as varchar(6)),''0'')+'''''',''+'        
END        
ELSE --presuming the data type is int,bit,numeric,decimal         
BEGIN        
 --SET @[email protected]+'''''''''+isnull(cast('[email protected]+' as varchar(200)),''0'')+'''''',''+'        
 --SET @[email protected]+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'[email protected]+',121)+'''''+''''',''NULL'')+'',121),''+'        
 SET @[email protected]+''''+'''+isnull('''''+'''''+convert(varchar(200),'[email protected]+')+'''''+''''',''NULL'')+'',''+'        
END        

SET @[email protected][email protected]+','        

FETCH NEXT FROM cursCol INTO @tableName , @colName,@dataType        
END        
DECLARE @Query nvarchar(4000)        

SET @query ='SELECT '''+substring(@string,0,len(@string)) + ') VALUES(''+ ' + substring(@stringData,0,len(@stringData)-2)+'''+'')'' FROM '[email protected]        
exec sp_executesql @query        
--select @query       

CLOSE cursCol        
DEALLOCATE cursCol        


  /*      

use poc     
go    

DECLARE @RC int      
DECLARE @domain_user varchar(50)      
DECLARE @tableName varchar(100)      

-- TODO: Set parameter values here.      
set @domain_user='yorgeorg'      
set @tableName = 'tbGui_WizardTabButtonAreas'      

EXECUTE @RC = [POC].[dbo].[procUtils_InsertGenerator]       
   @domain_user      
  ,@tableName      

*/
GO
YordanGeorgiev