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
595 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

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 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 Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

Suggested Solutions

Title # Comments Views Activity
SQL / Table Lock? 7 40
Load Fact table in SQL Server SSIS package 14 43
Server 2012 r2 and SQL 2014 6 34
SQL State HYT00. Timeout expired proplem 8 43
Have you tried to learn about Unicode, UTF-8, and multibyte text encoding and all the articles are just too "academic" or too technical? This article aims to make the whole topic easy for just about anyone to understand.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
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…

734 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