equivalenet of object_definition in sql2000

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 2000 Forums

Transact-SQL (2000)

equivalenet of object_definition in sql2000

Author

Topic

rtutus
Aged Yak Warrior

522 Posts

Posted - 2007-01-05 : 14:41:07

what s the equivalenet of object_definition in sql 2000 pls

This is not working for me in sql 2000. It works well in sql 2005

select object_definition(o.object_id),o.name from sys.objects as o

thanks a lot

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-01-06 : 02:10:55

What does it return in sql server 2005?

Madhivanan

Failing to plan is Planning to fail

rtutus
Aged Yak Warrior

522 Posts

Posted - 2007-01-06 : 12:50:19

I have for example this code:

exec sp_msforeachdb '
select ''?'', o.name, object_definition(o.object_id) from

.sys.objects as o where
object_definition(o.object_id) like ''%FileListInput%'' and o.type=''P'''

In sql 2005 it returns all SPs of all DBs including the keyword FileListInput
but it doesn t work in sql 2000
how can i replace object_definition with something similar sp_helptext or such to make my code work
Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-06 : 14:08:31

You got this answer "OBJECT_DEFINITION is a new function in SQL Server 2005. The nearest equivalent system stored procedure for this in SQL Server 2000 is the sp_helptext, which will only work for stored procedures, user-defined functions, triggers and views." here http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1080329&SiteID=1.

If you didn't understand it, you have to rewrite stored procedure sp_helptext into a function.

Peter Larsson
Helsingborg, Sweden

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-01-06 : 14:20:50

If you can use managed code from a client such as C# (instead of t-sql), you can use SMO to script each object in SQL 2000.

for an example of how to do this, have a look here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=73884

www.elsasoft.org

rtutus
Aged Yak Warrior

522 Posts

Posted - 2007-01-06 : 16:51:37

cool jezemine i didn t know your tool was in managed code
i ll need it next time for sure

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-01-06 : 19:35:22

One benefit of SMO is that it has the ability to script much more than what you can do with sp_helptext or syscomments. tables, indexes, and constraints for example.

www.elsasoft.org

rtutus
Aged Yak Warrior

522 Posts

Posted - 2007-01-08 : 11:38:32

There is a forum here to develop in .net inside sql server 2005. what s that mean. is that something new in sql 2005.
does that mean that i can use c# to develop functions and SP instead of T-sql or what exactly.
Thanks

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-01-08 : 16:01:13

yes, you can write sprocs/udfs/triggers/etc in managed code in SQL 2005.

It would be a bit weird to call SMO from inside a CLR proc though. Certainly possible however. If really you need the ability to generate scripts for any sort of object via a sproc call, that would be one way to do it though.

However I can't think of a valid case for such a need. why not just generate scripts with a separate app outside of sql server?

www.elsasoft.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources