quote:

---

Originally posted by Kristen

Personally I haven't found such a script that work. I don't know how SSMS does it, but all scripts I have seen (and sp_helptext) split overly long lines and truncate after the first page of SProc's script.

---

/* NOTE: Length of @SyscomText is 4000 to replace the length of
** text column in syscomments.
** lengths on @Line, #CommentText Text column and
** value for @DefinedLength are all 255. These need to all have
** the same values. 255 was selected in order for the max length
** display using down level clients
*/

quote:

---

Originally posted by Kristen

sp_helptext (source) says:

/* NOTE: Length of @SyscomText is 4000 to replace the length of
** text column in syscomments.
** lengths on @Line, #CommentText Text column and
** value for @DefinedLength are all 255. These need to all have
** the same values. 255 was selected in order for the max length
** display using down level clients
*/

so I assume that is organised to work with Query Analyser etc. with column width set to 256 characters.

There's a fairly significant amount of code in that SProc, so I doubt the "Select * From syscomments" style solutions are going to be up to much!

---

create table scripts(sps varchar(8000))
 
declare @texts varchar(1000)

select @texts='sp_helptext '''+'sp_helptext'+'''' 
EXEC('insert into scripts(sps) EXEC '+ @texts)         
insert into scripts(sps) 
select 'GO'
EXEC master..xp_cmdshell 'bcp "select * from dworksweb..scripts" queryout "c:\scripts.txt" -c -T' 
drop table scripts

quote:

---

Originally posted by amitranjan

SELECT O.Name as ProcName
,M.Definition as CreateScript
,O.Create_Date
,O.Modify_Date
FROM sys.sql_modules as M INNER JOIN sys.triggers as O
ON M.object_id = O.object_id

@ All
I tried above script from mangal pardeshi and found it working with a small change. After modified above to this

SELECT M.Definition as CreateScript
FROM sys.sql_modules as M INNER JOIN sys.triggers as O
ON M.object_id = O.object_id

this gave me all triggers of that database. Then i right clicked and exprted that as CSV file. Now opened it in Notepad++ or Sql Query Window and added

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

at the beginning of each triger and executed on the desired database. And it ran!!!..

amit Ranjan

---

quote:

---

Originally posted by webfred

quote:

---

Originally posted by amitranjan

SELECT O.Name as ProcName
,M.Definition as CreateScript
,O.Create_Date
,O.Modify_Date
FROM sys.sql_modules as M INNER JOIN sys.triggers as O
ON M.object_id = O.object_id

@ All
I tried above script from mangal pardeshi and found it working with a small change. After modified above to this

SELECT M.Definition as CreateScript
FROM sys.sql_modules as M INNER JOIN sys.triggers as O
ON M.object_id = O.object_id

this gave me all triggers of that database. Then i right clicked and exprted that as CSV file. Now opened it in Notepad++ or Sql Query Window and added

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

at the beginning of each triger and executed on the desired database. And it ran!!!..

amit Ranjan

---

Fine!

---

No, you're never too old to Yak'n'Roll if you're too young to die.

---

quote:

---

Originally posted by madhivanan
I still dont understand your point. Are you saying that usage of sp_helptext is not reliable?

---

quote:

---

Can you run this and see if all texts are exported?

---

output
----------------------------------------------------------------------------------- 
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Unable to open BCP host data-file
NULL

CREATE PROCEDURE TEMP_LongLine
AS
BEGIN
-- NOTE: This needs to be all on one line!

SELECT 1
END
GO
sp_helptext 'TEMP_LongLine'
GO
DROP PROCEDURE TEMP_LongLine
GO

Text
----------------------------------------------------------- 
CREATE PROCEDURE TEMP_LongLine
AS
BEGIN
--..!....1....!....2....!....3....!....4....!....5...   ...200....!....1....!....2....!....3....!....4....!....5....X
Y....6....!....7....!....8....!....9....!..300
SELECT 1
END

quote:

---

Originally posted by Kristen

If it was in a quoted string that would probably change the behaviour of the application (but not upset SQL)

sp_HelpText goes to a lot of trouble to split the text at 256 character boundary ...

... and also to bolt-back-together the multiple (8,000 byte) text pages ...

sys.sql_modules uses object_definition(sys.sysschobjs.id) - which seems to only get the first 8,000 characters ...

sp_HelpText is interrogating sys.syscomments - which itself is a VIEW to bolt-back-together again all the (new) tables that have scripts in them.

Would make more sense (with VARCHAR(MAX) now available etc.) to go straight to the underlying table I reckon ...

Maybe there is a, newer, replacement SProc for sp_HelpText that gets the whole script?

---