November 14, 2007 at 7:40 pm
I know there have to be at least a few out there...
Does anyone have a T-SQL script to generate the create table statement? It's one of the few operations for which I've always used the GUI. But this time around I have around 1,000 tables to script. That's way too much pointing and clicking for my tastes.
I did search but apparently my laziness extends to my google-fu today.
November 15, 2007 at 12:39 pm
This should do the trick. All you have to do is load some table variables with the name of tables you want created and the assoicated columns. This is just a basic create table shell, but you can easily customize it to suit your needs.
SET NOCOUNT ON
BEGIN TRY
BEGIN TRANSACTION
--===================================================
--Table to hold table names
--===================================================
declare @tables table
(
id int identity(1,1),
tablename varchar(25)
)
--insert table names into table
insert into @tables values('Test')
insert into @tables values('Test2')
insert into @tables values('Test3')
--===================================================
--===================================================
--Table to hold column names
--===================================================
declare @ColumnTable table
(
id int,
TableId int,
ColName varchar(255),
ColType varchar(25)
)
--====================================================
--to insert use the following format:
-- Field Number | TableID | Column Name | Column Type
--because we have 2 controls for the first table we
--give the first 1 and the second 2. The second table
--has 3 controls so we label them 1-3.
--====================================================
insert into @ColumnTable Values(1,1,'Field1','varchar(25)')
insert into @ColumnTable Values(2,1,'Field2','INT')
insert into @ColumnTable Values(1,2,'Field1','XML')
insert into @ColumnTable Values(2,2,'Field2','INT')
insert into @ColumnTable Values(3,2,'Field3','money')
insert into @ColumnTable Values(1,3,'Field1','varchar(25)')
insert into @ColumnTable Values(2,3,'Field2','smalldatetime')
insert into @ColumnTable Values(3,3,'Field3','XML')
insert into @ColumnTable Values(4,3,'Field4','INT')
insert into @ColumnTable Values(5,3,'Field5','money')
--===================================================
--hold dynamic sql statement
declare @sql nvarchar(max)
set @sql = ''
--hold current table name
declare @tablename varchar(100)
set @tablename = ''
--hold column data
declare @columns varchar(max)
set @columns = ''
--loop through table names
declare @TableCounter int
set @Tablecounter = 1
--loop through columns
declare @ColCounter int
--total number of tables to create
declare @NbrTables int
--total number of columns to create
declare @NbrColumns int
--get the total number of table names
set @NbrTables = (select count(*) from @tables)
while @TableCounter <= @NbrTables
begin
--reset column counter for loop
set @ColCounter = 1
set @columns = ''
--get table name
set @tablename = (select tablename from @tables where id = @Tablecounter)
--get the number of columns for the table being created
Set @NbrColumns = (select count(ct.id) from @ColumnTable ct where tableid = @tablecounter)
--while there are more columns add them
while @ColCounter <= @NbrColumns
begin
--if we are inserting the last column they syntax much change - see else
if @ColCounter <> @NbrColumns
begin
set @columns = @columns + (select ColName from @ColumnTable where tableid = @tableCounter and id = @Colcounter) + ' '
set @columns = @columns + (select ColType from @ColumnTable where tableid = @tableCounter and id = @Colcounter) + ' , '
end
else
begin
set @columns = @columns + (select ColName from @ColumnTable where tableid = @tableCounter and id = @Colcounter) + ' '
set @columns = @columns + (select ColType from @ColumnTable where tableid = @tableCounter and id = @Colcounter)
end
set @ColCounter = @ColCounter + 1
end
--put together sql statement
set @sql = N'
create table ' + @tablename + '(
' + @columns + '
)
'
--execute sql statement
execute sp_executesql @sql
--go to next table
set @tableCounter = @TableCounter + 1
end
PRINT CONVERT(VARCHAR(10),@NbrTables) + ' tables created sucessfully.'
COMMIT TRANSACTION
END TRY
BEGIN CATCH
PRINT 'Error Detected'
SELECT
ERROR_NUMBER() ERNumber,
ERROR_SEVERITY() Error_Severity,
ERROR_STATE() Error_State,
ERROR_PROCEDURE() Error_Procedure,
ERROR_LINE() Error_Line,
ERROR_MESSAGE() Error_Message
ROLLBACK TRANSACTION
END CATCH
SET NOCOUNT OFF
November 15, 2007 at 12:58 pm
I have a really simple solution, but I'm just to lazy to post it.
November 15, 2007 at 2:46 pm
Any reason you don't want to use the GUI to do the scripting? If you right click on the database name and select Tasks\Generate Scripts you will be in the scripting wizard. Its not as good as it was in 2000 but you can easily script out all or some of the tables or other objects. You have to put all of the scripts in the same file (which is a royal pain if you ask me) but it does a decent job of it.
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
November 15, 2007 at 3:09 pm
I know about the GUI - use it all the time. However, in this case I have 916 out of 3200+ tables to script. That's a lot of pointing and clicking.
I did find some examples using SMO but my VB is a bit too rusty to be immediately useful.
I am going to create a script to my likings (and soon), but things are a bit too time-sensitive to rely upon my rusty skills.
June 12, 2009 at 7:27 am
How to script each table in a separate file ?
June 12, 2009 at 7:51 am
Use information_schema.columns table you get most of from it
like table_name,column_name,max of length,is_nullable through which u can generate script easily..
June 12, 2009 at 8:40 am
I mean from SSMS / database/tasks/generate scripts ?
June 12, 2009 at 8:45 am
select table_name,column_name,data_type,maximum_character_length,is_nullable from information_schema.columns
this query gives you details and from them you can generate script as per your requirement..
but I think u r choosing laborious work.
June 12, 2009 at 8:47 am
mjarsaniya (6/12/2009)
Use information_schema.columns table you get most of from itlike table_name,column_name,max of length,is_nullable through which u can generate script easily..
In SSMS, right-click on the database, select Tasks.. Generate Scripts..
In the Script Wizard forms, the next to last one is "Output Option", under "Script to file" is an option to generate a file per object.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 12, 2009 at 10:04 am
OK, but my question was to generate to separate files per table, I could not find it.
June 12, 2009 at 12:51 pm
SQL Guy (6/12/2009)
OK, but my question was to generate to separate files per table, I could not find it.
Yes, that is what it will do. I have it right in front of me now. What version is your SSMS (get it from the About.. window)?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 12, 2009 at 2:23 pm
I think the original version of SQL 2005 did not let you script one file per table, but everyone should be on at least SP2 by now.
Is there a way to permanently change the defaults for table scripting at the beginning of the wizard ?
June 12, 2009 at 7:55 pm
homebrew01 (6/12/2009)
I think the original version of SQL 2005 did not let you script one file per table, but everyone should be on at least SP2 by now.Is there a way to permanently change the defaults for table scripting at the beginning of the wizard ?
Not sure what you mean by "at the beginning", but AFAIK there is no way to make a script file per object the default.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 17, 2009 at 2:26 pm
Why not setup profiler to run while you run the "Generate Script" from Studio? That should give you all the needed pieces. Then you would need to replace the "hard paths", like databasename.owner.table with a variable. You could then put that into a SProc and call sp_msforeachtable.
/* Anything is possible but is it worth it? */
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply