[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
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
Medium Priority
?
616 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
Independent Software Vendors: 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 23

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 23

Accepted Solution

by:
Snarf0001 earned 2000 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 Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…

649 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