Is it possible to get create/alter/drop scripts from within a SP in SQL server 2005?
-
Just throwing the question out there incase anyone has tried this. When you're using SQL Server Management Studio, you can right click on most objects and manually generate the create/drop/alter scripts. Is it possible to generate the SQL scripts (or send the script text to a parameter) from within a stored procedure? Thanks for any pointers!
"There's no such thing as a stupid question, only stupid people." - Mr. Garrison
-
Just throwing the question out there incase anyone has tried this. When you're using SQL Server Management Studio, you can right click on most objects and manually generate the create/drop/alter scripts. Is it possible to generate the SQL scripts (or send the script text to a parameter) from within a stored procedure? Thanks for any pointers!
"There's no such thing as a stupid question, only stupid people." - Mr. Garrison
I'm not aware of anything "built in" that would generate the create/alter/drop script for you, but the syntax of these commands is pretty straight forward. For example: IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FSACTIONSET]') AND type in (N'U')) DROP TABLE [dbo].[FSACTIONSET] So you could build your own SPROC to generate the script syntax. What are you trying to do ? Maybe there is a different way to attack your problem. david
-
I'm not aware of anything "built in" that would generate the create/alter/drop script for you, but the syntax of these commands is pretty straight forward. For example: IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FSACTIONSET]') AND type in (N'U')) DROP TABLE [dbo].[FSACTIONSET] So you could build your own SPROC to generate the script syntax. What are you trying to do ? Maybe there is a different way to attack your problem. david
Right, that would be from a drop script. I need to dynamically rebuild a large set of tables. There could be changes to the table on a daily basis, so I was hoping there would be a way to programmatically get at the create scripts and exec it as dynamic sql.
"There's no such thing as a stupid question, only stupid people." - Mr. Garrison
-
I'm not aware of anything "built in" that would generate the create/alter/drop script for you, but the syntax of these commands is pretty straight forward. For example: IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FSACTIONSET]') AND type in (N'U')) DROP TABLE [dbo].[FSACTIONSET] So you could build your own SPROC to generate the script syntax. What are you trying to do ? Maybe there is a different way to attack your problem. david
-
Right, that would be from a drop script. I need to dynamically rebuild a large set of tables. There could be changes to the table on a daily basis, so I was hoping there would be a way to programmatically get at the create scripts and exec it as dynamic sql.
"There's no such thing as a stupid question, only stupid people." - Mr. Garrison
Jon_Boy wrote:
There could be changes to the table on a daily basis
This tells me your design sucks. If you are changing table structure daily then there must be something disastrously wrong with your data design. I shudder to consider working in such an environment, it sounds like a nightmare.
-
I think I found a way to accomplish what I need. Thanks anyways.
"There's no such thing as a stupid question, only stupid people." - Mr. Garrison
-
Jon_Boy wrote:
I think I found a way to accomplish what I need
Care to share? It might help someone else.
Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP