September 8, 2011 at 5:56 am
A database has around 3102 tables and we want one script to get the create table script of 2000 tables.
Is it possible quickly using any query?
Thanks
September 8, 2011 at 6:02 am
Do you have a filter that can select the correct table names?
From there on out it's just a matter of how much details you need in the create script. This is not a small feat to script. It might be faster to save the ssis package that generates your scripts and reuse that even if it takes 1 hour to build the first time.
September 8, 2011 at 6:24 am
I have the table names saved in excel and notepad both.only excel filter is availble.
I want only want the complete ceate statement of the tables.
Thanks
September 8, 2011 at 6:37 am
Ya but do you need checks, defaults, triggers, unique constraints, filegroups, indexes, UDT...
This process is far from simple. I'm sure someone has tried it besides me but I gave up after a few days.
September 8, 2011 at 7:47 am
If you have to program this, I'd say the best way would be to use SMO. It has the ability to generate scripts in a very sophisticated manner. If you access it through PowerShell or some other call, it's pretty easy. There's a PowerShell script in my SQL Server Team-Based Development (free download) book that does the whole database, a little change or two would have it just do tables.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 8, 2011 at 9:06 am
create a directory of C:\Powershell on the server
copy the attached scripttables.txt file into the directory
rename scripttables.txt to scripttables.ps1
run powershell as an administrator
type sqlps and press enter
paste the below and press enter (change servernamehere and databasenamehere also if you are using a named instance change default to the instance name.
c:\powershell\scripttables.ps1 -server servernamehere -instance default -database databasenamehere -tables dbo.* -file c:\powershell\tables.sql
This will script the definition of all tables in the dbo schema to c:\powershell\tables.sql
Thanks
Chris
September 8, 2011 at 9:20 am
I love the scptfxr utility in SQL 2000, It made scripting full databases and individual objects easy!
September 8, 2011 at 9:24 am
chris.mcgowan (9/8/2011)
I love the scptfxr utility in SQL 2000, It made scripting full databases and individual objects easy!
Link to download for us youngsters? Or olders that lost the 2K cds?
September 8, 2011 at 10:23 am
It ships with SQL Server 7 and 2000 and is an undocumented executable. Link to resource below;
http://www.sqlserverclub.com/articles/use-scptxfr-to-schedule-sql-server-database-creation.aspx
Thanks
Chris
September 8, 2011 at 10:24 am
I will try and dig out some scripts with examples, I used to have one that would script all objects from every user database on an instance of SQL Server and upload it.
Thanks
Chris
September 8, 2011 at 10:30 am
chris.mcgowan (9/8/2011)
It ships with SQL Server 7 and 2000 and is an undocumented executable. Link to resource below;http://www.sqlserverclub.com/articles/use-scptxfr-to-schedule-sql-server-database-creation.aspx
Thanks
Chris
I must be blind, I don't see a download button for the exe.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply