Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Generate script

Author  Topic 

CanadaDBA

583 Posts

Posted - 2010-04-14 : 10:50:58
I need to script everything related to a table except the table itself. I don't need CREATE TABLE but need to generate scripts for constraints, FKs, PKs, and indexes. I have about 600 tables and is hard to do it manually. Any easy solution?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-14 : 14:03:47
You can do that with a tool such as Red Gate's SQL Compare. It's not possible with SSMS.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-14 : 14:51:04
I think the only way you can do that is to script the tables, balls and all. Then run it through a text filter that filters out all lines between and including lines starting with 'CREATE TABLE' and lines containing 'GO'. Something like:
do (
Read line
If line starts with 'CREATE TABLE' {
while line != 'GO' {
Read line
}
}
Write line
) while not EOF


------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.
Go to Top of Page

CanadaDBA

583 Posts

Posted - 2010-04-14 : 16:00:47
Well, it's not that easy as you mentioned in the above algorithm. Remember some lines have DEFAULT, etc. I did the work manually and my wrist hurts now!!

For the record, what I did:
- Generated script for all tables
- Prepared some templates like:

ALTER TABLE [schema].[TableName] ADD
DEFAULT Def1 FOR [Field1],
DEFAULT Def2 FOR [Field2]

/*****************************************/

ALTER TABLE [schema].[TableName] WITH NOCHECK
ADD CONSTRAINT [PK_Indices] PRIMARY KEY NONCLUSTERED
(
[FieldX] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]


Then all I had to do was to copy from the tables script and replace the red part in above templates and execute. And repeate for all tables.
- Finally, ran the PK/FK scripts which already was generated for the tables.


Canada DBA
Go to Top of Page
   

- Advertisement -