Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Dynamic Stored Proc to generate Create Table Script ,taking table_name as parameter Expand / Collapse
Author
Message
Posted Monday, March 17, 2008 1:30 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 22, 2010 12:25 PM
Points: 16, Visits: 61
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 ?
Post #470115
Posted Monday, March 17, 2008 7:20 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 9:14 AM
Points: 9,294, Visits: 9,483
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.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #470212
Posted Monday, March 17, 2008 8:10 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 22, 2010 12:25 PM
Points: 16, Visits: 61
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.
Post #470252
Posted Monday, March 17, 2008 9:54 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 9:14 AM
Points: 9,294, Visits: 9,483
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



-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #470331
Posted Monday, March 17, 2008 11:22 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 22, 2010 12:25 PM
Points: 16, Visits: 61
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
Post #470640
Posted Tuesday, March 18, 2008 6:38 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 9:14 AM
Points: 9,294, Visits: 9,483
Sorry, I haven't done SMO as CLR yet, just as a client call from a regular .net program.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #470754
Posted Thursday, July 23, 2009 9:00 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 26, 2014 4:51 PM
Points: 14, Visits: 246
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:


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.


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

Post #758309
Posted Thursday, July 23, 2009 9:59 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 9:14 AM
Points: 9,294, Visits: 9,483
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/


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #758389
Posted Wednesday, August 24, 2011 11:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 18, 2013 1:45 AM
Points: 1, Visits: 11
http://yoosufshanij.blogspot.com/2011/08/how-to-dynamically-create-table-using.html
Post #1164857
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse