[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 618
  • Last Modified:

is there a way to generate a sql script that include all the sprocs that the store procedure names are in ( "select SPECIFIC_NAME from information_schema.routines where ROUTINE_DEFINITION is NUll A

is there a way to generate a sql script that include all the sprocs that the store  procedure names are  in  ( "select SPECIFIC_NAME from information_schema.routines where ROUTINE_DEFINITION is NUll AND LAST_ALTERED > '2004-01-01'" ) using query analyzer?
0
musclejack
Asked:
musclejack
1 Solution
 
Jay ToopsCommented:
I did this

select specific_name from INFORMATION_SCHEMA.ROUTINES
where routine_definition is not null
and last_altered > '7/1/2004'
and it worked fine..
my database did not have ANY routines that were null

Jay
0
 
MarbryCommented:
Yes, just query on the sysobjects table joined with the syscomments table.  sysobjects stores the object name and syscomments stores the source text for the procedure.

/*
      Search for a string within the code of all the stored procs
      in the database.
*/
DECLARE @find1 varchar(50)
SET @find1 = 'ProcNameHere'

SELECT so.[name], so.refdate, scm.[text] FROM sysobjects so
JOIN syscomments scm ON so.id = scm.id
AND so.xtype = 'P'
AND so.status >= 0      
WHERE scm.[name] LIKE '%' + @find1 + '%'

Since you're talking about nested procedures, you'll still want to order them so that the lowest level nested procedures are scripted before any that reference them.  If you want to do ALL the user procedures just wrap a cursor around it, selecting the names from sysobjects to build a list from.

The one caveat you need to watch for is that if the proc text name happens to be split between rows in syscomments (it may not fit in one) you might not get a match on the name.  These have a sequence, the colid field, that you can order it by to concatenate the text back together though.

Marbry
0
 
Jay ToopsCommented:
oh i used the master database
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
musclejackAuthor Commented:
sorry, if i wasn't being clear.  what i want to do is: i don't want to generate sql script in enterprise mananger.  I want to generate a sql script(*.sql file) using query anaylzer that include all the files from  this  query ("select SPECIFIC_NAME from information_schema.routines where ROUTINE_DEFINITION is NUll AND LAST_ALTERED > '2004-01-01'")
0
 
Snarf0001Commented:
This should do the trick.  On the bcp line, make sure to enter in the proper database name, and the output file you want (instead of blah.sql).


declare @string varchar(8000)
declare @on varchar(60)
declare @off varchar(60)

set @on = char(13) + char(13) + 'SET QUOTED_IDENTIFIER ON' + char(13) +
'GO' + char(13) +
'SET ANSI_NULLS ON ' + char(13) +
'GO' + char(13) + char(13)

set @off =  char(13) + char(13) + 'GO' + char(13) +
'SET QUOTED_IDENTIFIER OFF ' + char(13) +
'GO' + char(13) +
'SET ANSI_NULLS ON ' + char(13) +
'GO' + char(13) + char(13)

select (@on + text + @off) as GenScript into tmpProcs from syscomments
where id in (select object_id(SPECIFIC_NAME) from information_schema.routines where ROUTINE_DEFINITION is NUll AND LAST_ALTERED > '2004-01-01')

set @string = 'bcp "select * from databasename.dbo.tmpProcs" queryout c:\blah.sql -c'

exec master.dbo.xp_cmdshell @string
drop table tmpProcs

0
 
Snarf0001Commented:
sorry, i just reread your post, i think you wanted individual files (one for each procedure).  The script above will make one file for everything, this one will generate one file for each proc:




declare @string varchar(8000)
declare @specname varchar(200)
declare @on varchar(60)
declare @off varchar(60)

set @on = char(13) + char(13) + 'SET QUOTED_IDENTIFIER ON' + char(13) +
'GO' + char(13) +
'SET ANSI_NULLS ON ' + char(13) +
'GO' + char(13) + char(13)

set @off =  char(13) + char(13) + 'GO' + char(13) +
'SET QUOTED_IDENTIFIER OFF ' + char(13) +
'GO' + char(13) +
'SET ANSI_NULLS ON ' + char(13) +
'GO' + char(13) + char(13)

declare cur cursor for
select SPECIFIC_NAME from information_schema.routines where ROUTINE_DEFINITION is NUll AND LAST_ALTERED > '2004-01-01'

create table tmpProcs (GenScript text)

open cur
fetch next from cur into @specname
while @@fetch_status=0
begin

      insert into tmpProcs (GenScript) select @on + text + @off from syscomments where id = object_id(@specname)

      set @string = 'bcp "select * from databasename.dbo.tmpProcs" queryout c:\' + rtrim(@specname) + '.sql -c'
      exec master.dbo.xp_cmdshell @string
      delete from tmpProcs

      fetch next from cur into @specname

end

close cur
deallocate cur

drop table tmpProcs
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now