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
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
588 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
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

860 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