INFORMATION_SCHEMA.Routines 2k5

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

General SQL Server Forums

New to SQL Server Programming

INFORMATION_SCHEMA.Routines 2k5

Author

Topic

X002548
Not Just a Number

15586 Posts

Posted - 2009-09-30 : 10:13:05

SELECT ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.Routines
WHERE ROUTINE_NAME = 'usp_INS_Company'

Why does ROUTINE_DEFINITION get truncated?

Am I missing a setting?

And I noticed that it went from 1-M rows, to 1-1 for ROUTINE_NAME in 2k5

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-30 : 10:20:40

Note that the datatype of ROUTINE_DEFINITION is nvarchar(4000)

Try

SELECT OBJECT_DEFINITION(OBJECT_ID('usp_INS_Company'))

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-30 : 10:23:35

BOL http://msdn.microsoft.com/en-us/library/ms188757%28SQL.90%29.aspx says

quote:

Returns the first 4000 characters of the definition text of the function or stored procedure if the function or stored procedure is not encrypted. Otherwise, returns NULL.

To ensure you obtain the complete definition, query the OBJECT_DEFINITION function or the definition column in the sys.sql_modules catalog view.

KH
[spoiler]Time is always against us[/spoiler]

X002548
Not Just a Number

15586 Posts

Posted - 2009-09-30 : 10:50:55

See....

That's why you Guys (SQLTeam) Are the best!!!!!!!!

Thanks a bunch...why wouldn't they make it varchar(max) I wonder

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2009-09-30 : 10:52:24

I was using


SELECT ROUTINE_NAME 
, SUBSTRING(ROUTINE_DEFINITION, CHARINDEX('BEGIN TRAN', ROUTINE_DEFINITION)- 3, 20)
, CHARINDEX('BEGIN TRAN', ROUTINE_DEFINITION)
, SUBSTRING(ROUTINE_DEFINITION, CHARINDEX('COMMIT TRAN', ROUTINE_DEFINITION)- 3, 20)
, CHARINDEX('COMMIT TRAN', ROUTINE_DEFINITION)
, SUBSTRING(ROUTINE_DEFINITION, CHARINDEX('ROLLBACK TRAN', ROUTINE_DEFINITION)- 3, 20)
, CHARINDEX('ROLLBACK TRAN', ROUTINE_DEFINITION)
FROM INFORMATION_SCHEMA.Routines
WHERE ROUTINE_NAME NOT LIKE 'dt%'
ORDER BY 1

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources