|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Get the script for an object using T-SQL (without using SQL-DMO)Is there any way to get the script (ddl) for an object (say a table) without
using SQL-DMO. I actually want to use only T-SQL to get the script for an object. For example, I may want to write a query that returns a list of objects that use a CASE statement by using a where clause that says something like: WHERE MYOBJECT.SCRIPT LIKE '%CASE%'. Oracle has a package called dbms_metadata.get_ddl which does this. Does SQL Server have something similar? DDL is not stored as is in any system tables for tables. But it is for other object types (views
stored procedures etc). See the syscomments table. -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "arch" <tony_oo***@yahoo.com> wrote in message news:1157181171.41465@angel.amnet.net.au... > Is there any way to get the script (ddl) for an object (say a table) without using SQL-DMO. I > actually want to use only T-SQL to get the script for an object. For example, I may want to write > a query that returns a list of objects that use a CASE statement by using a where clause that says > something like: WHERE MYOBJECT.SCRIPT LIKE '%CASE%'. Oracle has a package called > dbms_metadata.get_ddl which does this. Does SQL Server have something similar? > Thanks for the info. Is there anything I can do about generating table
scripts? Is there any .Net code (not SQL-DMO) that can do it? I don't want to use any COM objects (because I don't want the client machine to have to register it), but .Net code should be ok. I wonder what the rationale was for excluding tables? Seems to me Microsoft wasn't thinking too clearly. Show quote "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote in message news:uYFMdLmzGHA.4204@TK2MSFTNGP04.phx.gbl... > DDL is not stored as is in any system tables for tables. But it is for > other object types (views stored procedures etc). See the syscomments > table. > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://www.solidqualitylearning.com/ > > > "arch" <tony_oo***@yahoo.com> wrote in message > news:1157181171.41465@angel.amnet.net.au... >> Is there any way to get the script (ddl) for an object (say a table) >> without using SQL-DMO. I actually want to use only T-SQL to get the >> script for an object. For example, I may want to write a query that >> returns a list of objects that use a CASE statement by using a where >> clause that says something like: WHERE MYOBJECT.SCRIPT LIKE '%CASE%'. >> Oracle has a package called dbms_metadata.get_ddl which does this. Does >> SQL Server have something similar? >> > > I wonder what the rationale was for excluding tables? Seems to me Microsoft wasn't thinking too Possibly because the difficulty regarding ALTER TABLE, which isn't an all or nothing operation. You > clearly. can add columns, change datatype etc. ALTER PROCEDURE, ALTER VIEW etc. all replaces the whole definition. Anyhow, this is how it was done in version 1.0, which was the Sybase design, and my guess is that MS haven't had customer requests enough to justify development, test etc for this. The only code I know of , apart from DMO, that will generate CREATE TABLE from the system tables is SMO, the successor of DMO. It will most probably require FX 2.0. I'm not knowledgeable enough in ..NET to determine how plausible it is for you to redistribute SMO with your code, though. I bet that there is information about this in the 2005 version of Books Online. Regarding using DMO, did you consider using DMO on server-side? The server (2000 and 7.0) is bound to have DMO installed. You can instantiate COM objects using sp_OACreste etc. Google and you will find examples using DMO though sp_OACreste etc. for this particular purpose (generating scripts). -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "arch" <tony_oo***@yahoo.com> wrote in message news:1157187863.929277@angel.amnet.net.au... > Thanks for the info. Is there anything I can do about generating table scripts? Is there any > .Net code (not SQL-DMO) that can do it? I don't want to use any COM objects (because I don't want > the client machine to have to register it), but .Net code should be ok. > > I wonder what the rationale was for excluding tables? Seems to me Microsoft wasn't thinking too > clearly. > > > "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote in message > news:uYFMdLmzGHA.4204@TK2MSFTNGP04.phx.gbl... >> DDL is not stored as is in any system tables for tables. But it is for other object types (views >> stored procedures etc). See the syscomments table. >> >> -- >> Tibor Karaszi, SQL Server MVP >> http://www.karaszi.com/sqlserver/default.asp >> http://www.solidqualitylearning.com/ >> >> >> "arch" <tony_oo***@yahoo.com> wrote in message news:1157181171.41465@angel.amnet.net.au... >>> Is there any way to get the script (ddl) for an object (say a table) without using SQL-DMO. I >>> actually want to use only T-SQL to get the script for an object. For example, I may want to >>> write a query that returns a list of objects that use a CASE statement by using a where clause >>> that says something like: WHERE MYOBJECT.SCRIPT LIKE '%CASE%'. Oracle has a package called >>> dbms_metadata.get_ddl which does this. Does SQL Server have something similar? >>> >> > > I could be wrong, but I thought DMO was installed as part of the Client
Tools, not the Server installation (i'm thinking of SQL Server 2000). Can I be certain that DMO will always be installed on the server? Show quote "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote in message news:uCSSsCozGHA.3656@TK2MSFTNGP04.phx.gbl... >> I wonder what the rationale was for excluding tables? Seems to me >> Microsoft wasn't thinking too clearly. > > Possibly because the difficulty regarding ALTER TABLE, which isn't an all > or nothing operation. You can add columns, change datatype etc. ALTER > PROCEDURE, ALTER VIEW etc. all replaces the whole definition. > Anyhow, this is how it was done in version 1.0, which was the Sybase > design, and my guess is that MS haven't had customer requests enough to > justify development, test etc for this. > > The only code I know of , apart from DMO, that will generate CREATE TABLE > from the system tables is SMO, the successor of DMO. It will most probably > require FX 2.0. I'm not knowledgeable enough in .NET to determine how > plausible it is for you to redistribute SMO with your code, though. I bet > that there is information about this in the 2005 version of Books Online. > > Regarding using DMO, did you consider using DMO on server-side? The server > (2000 and 7.0) is bound to have DMO installed. You can instantiate COM > objects using sp_OACreste etc. Google and you will find examples using DMO > though sp_OACreste etc. for this particular purpose (generating scripts). > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://www.solidqualitylearning.com/ > > > "arch" <tony_oo***@yahoo.com> wrote in message > news:1157187863.929277@angel.amnet.net.au... >> Thanks for the info. Is there anything I can do about generating table >> scripts? Is there any .Net code (not SQL-DMO) that can do it? I don't >> want to use any COM objects (because I don't want the client machine to >> have to register it), but .Net code should be ok. >> >> I wonder what the rationale was for excluding tables? Seems to me >> Microsoft wasn't thinking too clearly. >> >> >> "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote >> in message news:uYFMdLmzGHA.4204@TK2MSFTNGP04.phx.gbl... >>> DDL is not stored as is in any system tables for tables. But it is for >>> other object types (views stored procedures etc). See the syscomments >>> table. >>> >>> -- >>> Tibor Karaszi, SQL Server MVP >>> http://www.karaszi.com/sqlserver/default.asp >>> http://www.solidqualitylearning.com/ >>> >>> >>> "arch" <tony_oo***@yahoo.com> wrote in message >>> news:1157181171.41465@angel.amnet.net.au... >>>> Is there any way to get the script (ddl) for an object (say a table) >>>> without using SQL-DMO. I actually want to use only T-SQL to get the >>>> script for an object. For example, I may want to write a query that >>>> returns a list of objects that use a CASE statement by using a where >>>> clause that says something like: WHERE MYOBJECT.SCRIPT LIKE '%CASE%'. >>>> Oracle has a package called dbms_metadata.get_ddl which does this. >>>> Does SQL Server have something similar? >>>> >>> >> >> > Why would you need the actual DDL? If you are looking for objects with
certain names etc you can get all of this via the Information_Schema views or thru one of the sp_helpxx commands. -- Show quoteAndrew J. Kelly SQL MVP "arch" <tony_oo***@yahoo.com> wrote in message news:1157195783.156712@angel.amnet.net.au... >I could be wrong, but I thought DMO was installed as part of the Client >Tools, not the Server installation (i'm thinking of SQL Server 2000). Can >I be certain that DMO will always be installed on the server? > > "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote > in message news:uCSSsCozGHA.3656@TK2MSFTNGP04.phx.gbl... >>> I wonder what the rationale was for excluding tables? Seems to me >>> Microsoft wasn't thinking too clearly. >> >> Possibly because the difficulty regarding ALTER TABLE, which isn't an all >> or nothing operation. You can add columns, change datatype etc. ALTER >> PROCEDURE, ALTER VIEW etc. all replaces the whole definition. >> Anyhow, this is how it was done in version 1.0, which was the Sybase >> design, and my guess is that MS haven't had customer requests enough to >> justify development, test etc for this. >> >> The only code I know of , apart from DMO, that will generate CREATE TABLE >> from the system tables is SMO, the successor of DMO. It will most >> probably require FX 2.0. I'm not knowledgeable enough in .NET to >> determine how plausible it is for you to redistribute SMO with your code, >> though. I bet that there is information about this in the 2005 version of >> Books Online. >> >> Regarding using DMO, did you consider using DMO on server-side? The >> server (2000 and 7.0) is bound to have DMO installed. You can instantiate >> COM objects using sp_OACreste etc. Google and you will find examples >> using DMO though sp_OACreste etc. for this particular purpose (generating >> scripts). >> >> -- >> Tibor Karaszi, SQL Server MVP >> http://www.karaszi.com/sqlserver/default.asp >> http://www.solidqualitylearning.com/ >> >> >> "arch" <tony_oo***@yahoo.com> wrote in message >> news:1157187863.929277@angel.amnet.net.au... >>> Thanks for the info. Is there anything I can do about generating table >>> scripts? Is there any .Net code (not SQL-DMO) that can do it? I don't >>> want to use any COM objects (because I don't want the client machine to >>> have to register it), but .Net code should be ok. >>> >>> I wonder what the rationale was for excluding tables? Seems to me >>> Microsoft wasn't thinking too clearly. >>> >>> >>> "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote >>> in message news:uYFMdLmzGHA.4204@TK2MSFTNGP04.phx.gbl... >>>> DDL is not stored as is in any system tables for tables. But it is for >>>> other object types (views stored procedures etc). See the syscomments >>>> table. >>>> >>>> -- >>>> Tibor Karaszi, SQL Server MVP >>>> http://www.karaszi.com/sqlserver/default.asp >>>> http://www.solidqualitylearning.com/ >>>> >>>> >>>> "arch" <tony_oo***@yahoo.com> wrote in message >>>> news:1157181171.41465@angel.amnet.net.au... >>>>> Is there any way to get the script (ddl) for an object (say a table) >>>>> without using SQL-DMO. I actually want to use only T-SQL to get the >>>>> script for an object. For example, I may want to write a query that >>>>> returns a list of objects that use a CASE statement by using a where >>>>> clause that says something like: WHERE MYOBJECT.SCRIPT LIKE '%CASE%'. >>>>> Oracle has a package called dbms_metadata.get_ddl which does this. >>>>> Does SQL Server have something similar? >>>>> >>>> >>> >>> >> > > I didn't want to apply a criterion against the name of the object, I wanted
the criterion against the script (ie to look inside the object) Show quote "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message news:%23VSBW7ozGHA.2636@TK2MSFTNGP06.phx.gbl... > Why would you need the actual DDL? If you are looking for objects with > certain names etc you can get all of this via the Information_Schema views > or thru one of the sp_helpxx commands. > > -- > Andrew J. Kelly SQL MVP > > "arch" <tony_oo***@yahoo.com> wrote in message > news:1157195783.156712@angel.amnet.net.au... >>I could be wrong, but I thought DMO was installed as part of the Client >>Tools, not the Server installation (i'm thinking of SQL Server 2000). Can >>I be certain that DMO will always be installed on the server? >> >> "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote >> in message news:uCSSsCozGHA.3656@TK2MSFTNGP04.phx.gbl... >>>> I wonder what the rationale was for excluding tables? Seems to me >>>> Microsoft wasn't thinking too clearly. >>> >>> Possibly because the difficulty regarding ALTER TABLE, which isn't an >>> all or nothing operation. You can add columns, change datatype etc. >>> ALTER PROCEDURE, ALTER VIEW etc. all replaces the whole definition. >>> Anyhow, this is how it was done in version 1.0, which was the Sybase >>> design, and my guess is that MS haven't had customer requests enough to >>> justify development, test etc for this. >>> >>> The only code I know of , apart from DMO, that will generate CREATE >>> TABLE from the system tables is SMO, the successor of DMO. It will most >>> probably require FX 2.0. I'm not knowledgeable enough in .NET to >>> determine how plausible it is for you to redistribute SMO with your >>> code, though. I bet that there is information about this in the 2005 >>> version of Books Online. >>> >>> Regarding using DMO, did you consider using DMO on server-side? The >>> server (2000 and 7.0) is bound to have DMO installed. You can >>> instantiate COM objects using sp_OACreste etc. Google and you will find >>> examples using DMO though sp_OACreste etc. for this particular purpose >>> (generating scripts). >>> >>> -- >>> Tibor Karaszi, SQL Server MVP >>> http://www.karaszi.com/sqlserver/default.asp >>> http://www.solidqualitylearning.com/ >>> >>> >>> "arch" <tony_oo***@yahoo.com> wrote in message >>> news:1157187863.929277@angel.amnet.net.au... >>>> Thanks for the info. Is there anything I can do about generating table >>>> scripts? Is there any .Net code (not SQL-DMO) that can do it? I don't >>>> want to use any COM objects (because I don't want the client machine to >>>> have to register it), but .Net code should be ok. >>>> >>>> I wonder what the rationale was for excluding tables? Seems to me >>>> Microsoft wasn't thinking too clearly. >>>> >>>> >>>> "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> >>>> wrote in message news:uYFMdLmzGHA.4204@TK2MSFTNGP04.phx.gbl... >>>>> DDL is not stored as is in any system tables for tables. But it is for >>>>> other object types (views stored procedures etc). See the syscomments >>>>> table. >>>>> >>>>> -- >>>>> Tibor Karaszi, SQL Server MVP >>>>> http://www.karaszi.com/sqlserver/default.asp >>>>> http://www.solidqualitylearning.com/ >>>>> >>>>> >>>>> "arch" <tony_oo***@yahoo.com> wrote in message >>>>> news:1157181171.41465@angel.amnet.net.au... >>>>>> Is there any way to get the script (ddl) for an object (say a table) >>>>>> without using SQL-DMO. I actually want to use only T-SQL to get the >>>>>> script for an object. For example, I may want to write a query that >>>>>> returns a list of objects that use a CASE statement by using a where >>>>>> clause that says something like: WHERE MYOBJECT.SCRIPT LIKE '%CASE%'. >>>>>> Oracle has a package called dbms_metadata.get_ddl which does this. >>>>>> Does SQL Server have something similar? >>>>>> >>>>> >>>> >>>> >>> >> >> > > Is it some place specific that you are searching "inside the object",
in case of a table ? Are you looking for the column names ? If yes, you can find those in the syscolumns table. Are you looking for any place that may contain an expression, like a check constraint or a column default ? If yes, you can find the definition of those objects in the syscomments table, along with the scripts for the procedures, views, functions and triggers. For example, you can use the following query: SELECT name, xtype FROM sysobjects WHERE id IN ( SELECT id FROM syscomments WHERE text LIKE '%CASE%' ) However, you need to take into account that the object definitions that are greater than 4KB are split into multiple rows in syscomments, and if the word you are looking for is just on a 4KB boundary, the above query may not find it. If you are using SQL Server 2000, you can make a query that is a bit more complicated to work around this problem. If you are using SQL Server 2005, there is a simple solution for this problem (thanks to the varchar(max) data type): SELECT name, type FROM sys.objects WHERE OBJECT_DEFINITION(id) LIKE '%CASE%' However, if you want to get the script of a table, in order to completely recreate that object (in another database or at a later time) with all the dependent objects (like indexes, constraints, triggers, permissions, etc), I'm afraid that using SQL-DMO or SMO would be your best option. Razvan > I thought DMO was installed as part of the Client Tools, Correct.> ... not the Server installation (I'm thinking of SQL Server 2000). Hmm, it was a while since I installed SQL2K. But, yes, as you mention it, the client components are probably optional when you install the server. I doubt you find many installations without the client components, but that could of course happen. -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "arch" <tony_oo***@yahoo.com> wrote in message news:1157195783.156712@angel.amnet.net.au... >I could be wrong, but I thought DMO was installed as part of the Client Tools, not the Server >installation (i'm thinking of SQL Server 2000). Can I be certain that DMO will always be installed >on the server? > > "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote in message > news:uCSSsCozGHA.3656@TK2MSFTNGP04.phx.gbl... >>> I wonder what the rationale was for excluding tables? Seems to me Microsoft wasn't thinking too >>> clearly. >> >> Possibly because the difficulty regarding ALTER TABLE, which isn't an all or nothing operation. >> You can add columns, change datatype etc. ALTER PROCEDURE, ALTER VIEW etc. all replaces the whole >> definition. >> Anyhow, this is how it was done in version 1.0, which was the Sybase design, and my guess is that >> MS haven't had customer requests enough to justify development, test etc for this. >> >> The only code I know of , apart from DMO, that will generate CREATE TABLE from the system tables >> is SMO, the successor of DMO. It will most probably require FX 2.0. I'm not knowledgeable enough >> in .NET to determine how plausible it is for you to redistribute SMO with your code, though. I >> bet that there is information about this in the 2005 version of Books Online. >> >> Regarding using DMO, did you consider using DMO on server-side? The server (2000 and 7.0) is >> bound to have DMO installed. You can instantiate COM objects using sp_OACreste etc. Google and >> you will find examples using DMO though sp_OACreste etc. for this particular purpose (generating >> scripts). >> >> -- >> Tibor Karaszi, SQL Server MVP >> http://www.karaszi.com/sqlserver/default.asp >> http://www.solidqualitylearning.com/ >> >> >> "arch" <tony_oo***@yahoo.com> wrote in message news:1157187863.929277@angel.amnet.net.au... >>> Thanks for the info. Is there anything I can do about generating table scripts? Is there any >>> .Net code (not SQL-DMO) that can do it? I don't want to use any COM objects (because I don't >>> want the client machine to have to register it), but .Net code should be ok. >>> >>> I wonder what the rationale was for excluding tables? Seems to me Microsoft wasn't thinking too >>> clearly. >>> >>> >>> "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote in message >>> news:uYFMdLmzGHA.4204@TK2MSFTNGP04.phx.gbl... >>>> DDL is not stored as is in any system tables for tables. But it is for other object types >>>> (views stored procedures etc). See the syscomments table. >>>> >>>> -- >>>> Tibor Karaszi, SQL Server MVP >>>> http://www.karaszi.com/sqlserver/default.asp >>>> http://www.solidqualitylearning.com/ >>>> >>>> >>>> "arch" <tony_oo***@yahoo.com> wrote in message news:1157181171.41465@angel.amnet.net.au... >>>>> Is there any way to get the script (ddl) for an object (say a table) without using SQL-DMO. I >>>>> actually want to use only T-SQL to get the script for an object. For example, I may want to >>>>> write a query that returns a list of objects that use a CASE statement by using a where clause >>>>> that says something like: WHERE MYOBJECT.SCRIPT LIKE '%CASE%'. Oracle has a package called >>>>> dbms_metadata.get_ddl which does this. Does SQL Server have something similar? >>>>> >>>> >>> >>> >> > >  |
|||||||||||||||||||||||
Other interesting topics