Dynamic Stored Proc to generate Create Table Script ,taking table_name as parameter

Question

Dynamic Stored Proc to generate Create Table Script ,taking table_name as parameter

Alkesh Khedle

Right there with Babe

Points: 780
More actions
March 17, 2008 at 1:30 am

#185283

Hi I am looking to make Dynamic Stored Proc to generate Create Table Script ,taking table_name as parameter.Is any system stored proc availabel in SQL Server2008 to do the same ?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

There is a SQL Server 2008 forum where you might get a better answer for that part of your question.

As for SQL2005, although it is easy to do from SMO, Tables are one of the few common SQL objects that you cannot generate a script for with just SQL, right out of the box. There are some custom stored procedures around that might be sufficient for you, I'll let those more familiar with them comment on that.

The other way to go, would be to write a CLR UDF that just called SMO and returned the script. I have thought about writing such a Function, and might in the near future.

[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]

Alkesh Khedle Right there with Babe Points: 780 More actions · Answer 2

Hi

I Tried to use CLR UDF to achive the same but I didnt get the result .

Can I get the proper name and the procedure to use call the functions .

We need to activate some procedures through Surface Area configuration also to achive the same .Please send the proper doc .

Thansk in advance.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

Well, I have to confess, I have not written a CLR UDF yet (I am hoping to write my first this week).

However, I have written a lot of SMO. Here is a utility function that should be easily adaptable to a CLR UDF:

Imports Microsoft.SqlServer

Imports Microsoft.SqlServer.Management

Imports Microsoft.SqlServer.Management.Common

Imports Microsoft.SqlServer.Management.Smo

Imports Microsoft.SqlServer.Server

Imports system.Data

Module Util

Public Function ScriptTable(ByVal Table As Smo.Table, ByVal Options As Smo.ScriptingOptions) As String

' This function generates the script for an SMO.Table object based on

'the options specified and then returns is as a single concatenated

'string with CR/LF's between the lines.

'

' RBarryYoung@Gmail.com, 17-Mar-2008

'First, get the script

Dim scrLines As New Collections.Specialized.StringCollection

scrLines = Table.Script(Options)

'Now, turn the Script=String() into 1 String with CrLf's

Dim strScript As String = "", strLines() As String

If scrLines.Count > 0 Then

ReDim strLines(scrLines.Count - 1)

scrLines.CopyTo(strLines, 0)

strScript = Join(strLines, vbCrLf)

End If

Return strScript

End Function

End Module

[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]

Alkesh Khedle Right there with Babe Points: 780 More actions · Answer 4

Hi Barry thanks for the solution .

Can u plz tell me how to call these function in sql server. I never called SMO's in SQL Server yet.Is that required any assamblly to call or sm thing ??

Thanks

Alkesh K.

SQL Server DB Developer

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

Sorry, I haven't done SMO as CLR yet, just as a client call from a regular .net program.

[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]

GBear SSC Enthusiast Points: 165 More actions · Answer 6

Hi,

SMO is currently not supported in CLR extended stored procedures. If you try to construct an extended stored proc to call SMO and install it into SQL 2005, you will get the error:

[font="Courier New"]

Assembly 'xxxxxxxx' references assembly 'microsoft.sqlserver.batchparser, version=9.0.242.0, culture=neutral, publickeytoken=89845dcd8080cc91.', which is not present in the current database. SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from, but that operation has failed (reason: 2(The system cannot find the file specified.)). Please load the referenced assembly into the current database and retry your request.

[/font]

For which there is no workaround. Please refer to the Microsoft issue below, and add your vote to have Microsoft add this feature.

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126386

GrayB

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

Yep, I did discover this about a month after I wrote this.

For an alternative solution, check this script out: http://www.sqlservercentral.com/scripts/Miscellaneous/30730/

[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]

yoosufshanij SSC Journeyman Points: 79 More actions · Answer 8

http://yoosufshanij.blogspot.com/2011/08/how-to-dynamically-create-table-using.html