Solved

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

Posted on 2004-09-22
6
592 Views
Last Modified: 2013-11-18
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
Comment
Question by:musclejack
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 10

Expert Comment

by:Jay Toops
ID: 12125714
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
 

Expert Comment

by:Marbry
ID: 12125719
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
 
LVL 10

Expert Comment

by:Jay Toops
ID: 12125721
oh i used the master database
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:musclejack
ID: 12125943
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
 
LVL 22

Expert Comment

by:Snarf0001
ID: 12127137
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
 
LVL 22

Accepted Solution

by:
Snarf0001 earned 500 total points
ID: 12127265
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

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The viewer will learn how to dynamically set the form action using jQuery.
HTML5 has deprecated a few of the older ways of showing media as well as offering up a new way to create games and animations. Audio, video, and canvas are just a few of the adjustments made between XHTML and HTML5. As we learned in our last micr…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question