Saving Labor

Question

Saving Labor

Steve Jones - SSC Editor

SSC Guru

Points: 736173
More actions
September 5, 2022 at 12:00 am

#4078047

Comments posted to this topic are about the item Saving Labor

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Carlo Romagnano SSC-Insane Points: 22668 More actions · Answer 1

Try this (adjust for your needs), then execute the output script:

set nocount on
select DISTINCT 'exec sp_helptext ' AS '--','[' + o.name + ']' AS [ ],CASE OBJECTPROPERTY(o.id,'ExecIsTriggerDisabled') WHEN 1 THEN '--Disabled' ELSE '' END AS [---]
from sysobjects o(readuncommitted) where o.name COLLATE SQL_Latin1_General_CP1_CI_AS
like '%%'
and o.xtype IN(''
,'P '--: stored procedure'
--,'IF'/*: inline function'*/,'FN'/*: scalar function'*/,'TF'/*: table function'*/
--,'TR'--: trigger'
--,'AP'--: application'
--,'V '--: view'
--,'U '--: user table'
--,'S '--: system table'
--,'C '--: check cns'
--,'F '--: foreign key cns'
--,'D '--: default (maybe cns)'
--,'PK'--: primary key cns'
--,'UQ'--: unique key cns'
--,'X '--: extended stored proc'
)
and o.name not like 'dt[_]%'
order by [---],[ ]
set nocount off

The trick is the store procedure sp_helptext.

This version also works starting from Sql2000

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

Mmmm... the question said "without spending a lot of time", so I chose the fourth option!

John

Edit: scrub that - I didn't read the Explanation properly.

This reply was modified 1 year, 6 months ago by John Mitchell-245523.

Steve Jones - SSC Editor SSC Guru Points: 736173 More actions · Answer 3

Carlo Romagnano wrote:

...
This version also works starting from Sql2000

It does, but doesn't save the scripts to a file(s).