Fragmented array: procedure

Showing posts with label procedure. Show all posts

Thursday, March 29, 2012

Finding latest full backup file name

We are instituting a procedure to restore the latest full backup (*.BAK
extension) of our production database to a training database in an automated
fashion. Is there a way to determine the pathname and filename of the latest
full backup of a database (from msdb perhaps). or do I need to use DOS Dir
command manipulation to get this information from file timestamps? I would
then use T-SQL to restore the filename over the existing training database.
Any ideas?
--
Larry Menzin
American Techsystems Corp.If you are not using multiple devices, may be this can help.
use msdb
go
select top 1
a.[name],
a.[database_name],
a.type,
a.backup_start_date,
a.backup_finish_date,
c.physical_device_name
from
dbo.backupset as a
inner join
dbo.backupmediaset as b
on a.media_set_id = b.media_set_id
inner join
dbo.backupmediafamily as c
on b.media_set_id = c.media_set_id
where
a.[database_name] = 'northwind'
and a.type = 'D'
order by
a.backup_finish_date desc
AMB
"Larry Menzin" wrote:
> We are instituting a procedure to restore the latest full backup (*.BAK
> extension) of our production database to a training database in an automated
> fashion. Is there a way to determine the pathname and filename of the latest
> full backup of a database (from msdb perhaps). or do I need to use DOS Dir
> command manipulation to get this information from file timestamps? I would
> then use T-SQL to restore the filename over the existing training database.
> Any ideas?
> --
> Larry Menzin
> American Techsystems Corp.|||Just what I was looking for. Thanks a lot.
--
Larry Menzin
American Techsystems Corp.
"Alejandro Mesa" wrote:
> If you are not using multiple devices, may be this can help.
> use msdb
> go
> select top 1
> a.[name],
> a.[database_name],
> a.type,
> a.backup_start_date,
> a.backup_finish_date,
> c.physical_device_name
> from
> dbo.backupset as a
> inner join
> dbo.backupmediaset as b
> on a.media_set_id = b.media_set_id
> inner join
> dbo.backupmediafamily as c
> on b.media_set_id = c.media_set_id
> where
> a.[database_name] = 'northwind'
> and a.type = 'D'
> order by
> a.backup_finish_date desc
>
> AMB
>
> "Larry Menzin" wrote:
> > We are instituting a procedure to restore the latest full backup (*.BAK
> > extension) of our production database to a training database in an automated
> > fashion. Is there a way to determine the pathname and filename of the latest
> > full backup of a database (from msdb perhaps). or do I need to use DOS Dir
> > command manipulation to get this information from file timestamps? I would
> > then use T-SQL to restore the filename over the existing training database.
> > Any ideas?
> >
> > --
> > Larry Menzin
> > American Techsystems Corp.

Finding latest full backup file name

We are instituting a procedure to restore the latest full backup (*.BAK
extension) of our production database to a training database in an automated
fashion. Is there a way to determine the pathname and filename of the latest
full backup of a database (from msdb perhaps). or do I need to use DOS Dir
command manipulation to get this information from file timestamps? I would
then use T-SQL to restore the filename over the existing training database.
Any ideas?
Larry Menzin
American Techsystems Corp.
If you are not using multiple devices, may be this can help.
use msdb
go
select top 1
a.[name],
a.[database_name],
a.type,
a.backup_start_date,
a.backup_finish_date,
c.physical_device_name
from
dbo.backupset as a
inner join
dbo.backupmediaset as b
on a.media_set_id = b.media_set_id
inner join
dbo.backupmediafamily as c
on b.media_set_id = c.media_set_id
where
a.[database_name] = 'northwind'
and a.type = 'D'
order by
a.backup_finish_date desc
AMB
"Larry Menzin" wrote:

> We are instituting a procedure to restore the latest full backup (*.BAK
> extension) of our production database to a training database in an automated
> fashion. Is there a way to determine the pathname and filename of the latest
> full backup of a database (from msdb perhaps). or do I need to use DOS Dir
> command manipulation to get this information from file timestamps? I would
> then use T-SQL to restore the filename over the existing training database.
> Any ideas?
> --
> Larry Menzin
> American Techsystems Corp.
|||Just what I was looking for. Thanks a lot.
Larry Menzin
American Techsystems Corp.
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> If you are not using multiple devices, may be this can help.
> use msdb
> go
> select top 1
> a.[name],
> a.[database_name],
> a.type,
> a.backup_start_date,
> a.backup_finish_date,
> c.physical_device_name
> from
> dbo.backupset as a
> inner join
> dbo.backupmediaset as b
> on a.media_set_id = b.media_set_id
> inner join
> dbo.backupmediafamily as c
> on b.media_set_id = c.media_set_id
> where
> a.[database_name] = 'northwind'
> and a.type = 'D'
> order by
> a.backup_finish_date desc
>
> AMB
>
> "Larry Menzin" wrote:

Finding latest full backup file name

We are instituting a procedure to restore the latest full backup (*.BAK
extension) of our production database to a training database in an automated
fashion. Is there a way to determine the pathname and filename of the latest
full backup of a database (from msdb perhaps). or do I need to use DOS Dir
command manipulation to get this information from file timestamps? I would
then use T-SQL to restore the filename over the existing training database.
Any ideas?
Larry Menzin
American Techsystems Corp.If you are not using multiple devices, may be this can help.
use msdb
go
select top 1
a.[name],
a.[database_name],
a.type,
a.backup_start_date,
a.backup_finish_date,
c.physical_device_name
from
dbo.backupset as a
inner join
dbo.backupmediaset as b
on a.media_set_id = b.media_set_id
inner join
dbo.backupmediafamily as c
on b.media_set_id = c.media_set_id
where
a.[database_name] = 'northwind'
and a.type = 'D'
order by
a.backup_finish_date desc
AMB
"Larry Menzin" wrote:

> We are instituting a procedure to restore the latest full backup (*.BAK
> extension) of our production database to a training database in an automat
ed
> fashion. Is there a way to determine the pathname and filename of the late
st
> full backup of a database (from msdb perhaps). or do I need to use DOS Dir
> command manipulation to get this information from file timestamps? I would
> then use T-SQL to restore the filename over the existing training database
.
> Any ideas?
> --
> Larry Menzin
> American Techsystems Corp.|||Just what I was looking for. Thanks a lot.
Larry Menzin
American Techsystems Corp.
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> If you are not using multiple devices, may be this can help.
> use msdb
> go
> select top 1
> a.[name],
> a.[database_name],
> a.type,
> a.backup_start_date,
> a.backup_finish_date,
> c.physical_device_name
> from
> dbo.backupset as a
> inner join
> dbo.backupmediaset as b
> on a.media_set_id = b.media_set_id
> inner join
> dbo.backupmediafamily as c
> on b.media_set_id = c.media_set_id
> where
> a.[database_name] = 'northwind'
> and a.type = 'D'
> order by
> a.backup_finish_date desc
>
> AMB
>
> "Larry Menzin" wrote:
>

Wednesday, March 28, 2012

Finding dependency of Objects in SQL Server 2000

Hello,

I would like to know , how can we find the objects dependency in SQL Server.

e.g If there is a Stored Procedure (sp_GetShowEmp) , I need to find On which tables,views,Stored Procedures,Functions is dependent. And Name of the other objects which are dependent on the Stored Procedure (sp_GetShowEmp).

Thanks

Sidheshwar

You can take help of SP_DEPENDS in this case to show the dependancy of other objects on a table or trigger or view.

Monday, March 26, 2012

Finding all references to a column

I have a task to where I need to move a column from one table to
another. I want to be sure I update any view, stored procedure,
trigger, etc. that references the column. I simply want a query that
will report the related objects and then I will update them manually
but before I go and try and figure out how to do this by querying the
sys tables is there an sp_sproc that will do this?[posted and mailed, please reply in news]

rnewman (newmanr19@.yahoo.com) writes:
> I have a task to where I need to move a column from one table to
> another. I want to be sure I update any view, stored procedure,
> trigger, etc. that references the column. I simply want a query that
> will report the related objects and then I will update them manually
> but before I go and try and figure out how to do this by querying the
> sys tables is there an sp_sproc that will do this?

The best way is to build the database from scripts, with the column
reomved, and then look through all errors you get.

You can also run this query:

select object_name(id)
from sysdepends
where depid = object_id('tbl')
and col_name(depid, depnumber) = 'col'
order by 1

However, this may not be reliable. If you can be confident that all
procedures abd views have been created/altered after the table was
created, this will work. But if you have dropped the table and replaced
with a new version, or you loaded stored procedures before you created
the table, the dependency information will be incomplete.

Note: while the sysdepends tables is documented in Books Online,
the usage of the depnumber as column id is undocumented, and may
be subject to change without notice.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Finding affected tables

I am attempting to document a sql server 2000 based accounting system.
Is there any way to see what tables a stored procedure affects
without diving into the code?

Regards,

Ty"Tyler Hudson" <tylerh@.allpax.com> wrote in message
news:804fa945.0403020849.fcf8f0b@.posting.google.co m...
> I am attempting to document a sql server 2000 based accounting system.
> Is there any way to see what tables a stored procedure affects
> without diving into the code?
>
> Regards,
> Ty

You can try sp_depends, although it may not be completely accurate,
depending on the order in which objects were created.

Simon

Friday, March 23, 2012

Find what procedure was execd

Hello

I need to find out what stored procedure was executed when I click on
a button in a third party application, is there a way to find this
information out in some sort of log?

Some kind of profiling to see what procedure was executed and with
what parameters.

Kind Regards

Matt
www.fruitsalad.org"Matt" <matt@.fruitsalad.org> wrote in message
news:b609190f.0312130930.377f4d7c@.posting.google.c om...
> Hello
> I need to find out what stored procedure was executed when I click on
> a button in a third party application, is there a way to find this
> information out in some sort of log?
> Some kind of profiling to see what procedure was executed and with
> what parameters.
> Kind Regards
> Matt
> www.fruitsalad.org

You can use SQL Profiler to trace all the SQL sent to the server - see Books
Online for details.

Simon

Wednesday, March 21, 2012

find type of a column

is it possible to find a column's type inside stored procedure? I am going to get the name of the table and then work with the columns and I just need the type of the column if I have had the name of it

regards

Maybe something like this:

use tempdb
go
create table ##typeExample (anotherType integer, targetType double precision)
declare @.objectName sysname set @.objectName = '##typeExample'
declare @.columnName sysname set @.columnName = 'targetType'
select c.colid,
c.[name] as columnName,
t.[name] as type
from sysobjects o
inner join syscolumns c
on o.id = c.id
and o.[name] = @.objectName
and c.[name] = @.columnName
-- and o.type = 'U' -- Uncomment if you want only user tables
-- and o.type in ('U','V') -- Uncomment if you want tables and views
inner join systypes t
on c.xtype = t.xtype
-- - Output: -
-- colid columnName type
-- -
-- 2 targetType float
go
drop table ##typeExample
go

Dave

Find the Stored procedure

Hello,
Our SQL machine is getting bogged down by some sort of stored procedure
and I am trying to find which one. My SQLdiagnostic software (by Idera)
that monitors our SQL server, says that these commands are executing
and taking upwards of 30 minutes to run. This is new and unexpected.

The commands are:

exec sp_executesql @.Pm0 = 0x683AAD4E8159A84C90B65216A4DA25DE, @.Pm1 =
25, @.Pm2 = 2, @.Pm3 = 1
exec sp_executesql @.Pm0 = 0x683AAD4E8159A84C90B65216A4DA25DE, @.Pm1 =
105, @.Pm2 = 2, @.Pm3 = 1
exec sp_executesql @.Pm0 = 0x683AAD4E8159A84C90B65216A4DA25DE, @.Pm1 =
57, @.Pm2 = 2, @.Pm3 = 1

I am getting pages of these and yesterday the are taking upto 30
minutes to run (currently they are taking 1-2 minutes to complete w/o
people on the machine).

We are not getting much help from our software vendor (of our
admissions software, not Idera) on this matter. I have sa access to the
SQL machine and I can see the pages and pages of stored procedures, but
I don't know what the above is running. I want to find the stored
procedure that keeps getting executed. Is the @.Pm0 = an encrypted
entry?

Any advice I would appreciate.
Thanks
Rob Camardarcamarda wrote:
> Hello,
> Our SQL machine is getting bogged down by some sort of stored procedure
> and I am trying to find which one. My SQLdiagnostic software (by Idera)
> that monitors our SQL server, says that these commands are executing
> and taking upwards of 30 minutes to run. This is new and unexpected.
> The commands are:
> exec sp_executesql @.Pm0 = 0x683AAD4E8159A84C90B65216A4DA25DE, @.Pm1 =
> 25, @.Pm2 = 2, @.Pm3 = 1
> exec sp_executesql @.Pm0 = 0x683AAD4E8159A84C90B65216A4DA25DE, @.Pm1 =
> 105, @.Pm2 = 2, @.Pm3 = 1
> exec sp_executesql @.Pm0 = 0x683AAD4E8159A84C90B65216A4DA25DE, @.Pm1 =
> 57, @.Pm2 = 2, @.Pm3 = 1
> I am getting pages of these and yesterday the are taking upto 30
> minutes to run (currently they are taking 1-2 minutes to complete w/o
> people on the machine).
> We are not getting much help from our software vendor (of our
> admissions software, not Idera) on this matter. I have sa access to the
> SQL machine and I can see the pages and pages of stored procedures, but
> I don't know what the above is running. I want to find the stored
> procedure that keeps getting executed. Is the @.Pm0 = an encrypted
> entry?
> Any advice I would appreciate.
> Thanks
> Rob Camarda

The replies to this post suggest that the call may be coming from the
..NET SqlClient, although it seems strange that you don't see the actual
command parameter passed to sp_executesql:

http://groups.google.co.uk/group/mi...91b9670a?hl=en&

But beyond that I have absolutely no idea - you might try posting in
microsoft.public.dotnet.framework.adonet to see if anyone can give you a
clue on how to investigate further.

Simon|||Hi

sp_executesql is documented in BOL the first parameter is a Unicode string
to be executed. you may want to run SQL profiler to find out what the
commands are.

John

"rcamarda" <rcamarda@.cablespeed.com> wrote in message
news:1126614746.141363.266270@.f14g2000cwb.googlegr oups.com...
> Hello,
> Our SQL machine is getting bogged down by some sort of stored procedure
> and I am trying to find which one. My SQLdiagnostic software (by Idera)
> that monitors our SQL server, says that these commands are executing
> and taking upwards of 30 minutes to run. This is new and unexpected.
> The commands are:
> exec sp_executesql @.Pm0 = 0x683AAD4E8159A84C90B65216A4DA25DE, @.Pm1 =
> 25, @.Pm2 = 2, @.Pm3 = 1
> exec sp_executesql @.Pm0 = 0x683AAD4E8159A84C90B65216A4DA25DE, @.Pm1 =
> 105, @.Pm2 = 2, @.Pm3 = 1
> exec sp_executesql @.Pm0 = 0x683AAD4E8159A84C90B65216A4DA25DE, @.Pm1 =
> 57, @.Pm2 = 2, @.Pm3 = 1
> I am getting pages of these and yesterday the are taking upto 30
> minutes to run (currently they are taking 1-2 minutes to complete w/o
> people on the machine).
> We are not getting much help from our software vendor (of our
> admissions software, not Idera) on this matter. I have sa access to the
> SQL machine and I can see the pages and pages of stored procedures, but
> I don't know what the above is running. I want to find the stored
> procedure that keeps getting executed. Is the @.Pm0 = an encrypted
> entry?
> Any advice I would appreciate.
> Thanks
> Rob Camardasql

Monday, March 12, 2012

find right database for stored procedure

I have a message queueing system set up so that applications can insert
message information into a database using a stored procedure in the master
database...
CREATE PROCEDURE sp_PutInQueue
<...message parameters...>
AS
DECLARE @.dbid smallint
--get the database context who is running this procedure
SET @.dbid = db_id()
INSERT INTO MessageQueue.Queue (
<...message parameter fields...>,
dbID)
VALUES (
<...message parameters...>,
@.dbid)
GO
After I process the message, I want to use the stored dbID in the table to
reference the calling application and execute a stored procedure in that
database so it will know it's message has been processed. Since I can't use
the USE clause in a stored procedure, how can I accomplish what I am trying
to do'--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Use dynamic SQL & a scope:
-- start in master db
declare @.sql varchar(200)
declare @.dbid varchar(30)
set @.dbid = 'pubs'
set @.sql = 'use ' + @.dbid + ' select top 2 * from authors'
-- or for an SP: set @.sql = 'exec ' + @.dbid + '..MySp param1, param2'
execute (@.sql)
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQksjIoechKqOuFEgEQKqCwCghzNcxXMIEnfQ
pw55MCLSObS1Ld8Anit2
R+yW+XNslP/Rm6wTVEBmA243
=MqHA
--END PGP SIGNATURE--
Random wrote:
> I have a message queueing system set up so that applications can insert
> message information into a database using a stored procedure in the master
> database...
> CREATE PROCEDURE sp_PutInQueue
> <...message parameters...>
> AS
> DECLARE @.dbid smallint
> --get the database context who is running this procedure
> SET @.dbid = db_id()
> INSERT INTO MessageQueue.Queue (
> <...message parameter fields...>,
> dbID)
> VALUES (
> <...message parameters...>,
> @.dbid)
> GO
> After I process the message, I want to use the stored dbID in the table to
> reference the calling application and execute a stored procedure in that
> database so it will know it's message has been processed. Since I can't u
se
> the USE clause in a stored procedure, how can I accomplish what I am tryin
g
> to do'

Find out the percentage complete of Stored Procedure

hi,

In my code i have to increment progress bar based on percentage
completion of Stored Procedure.I am not to get any solution on this.
Please Help me on this issue.
I am using win forms Visual studio 2005 and Sql server 2005.

Thanx in advance

NitinThere is no way to measure the progress of a single SQL command, so
what you an do depends on what is going on inside the stored
procedure. If the procedure has multiple steps you could probably
return something between each that would let the front end indicate
progress. If all the time is in one big SELECT or UPDATE or whatever,
then you can not show true progress.

Of course you could cheat and just pretend to show progress. I have
long been convinced that many progress bars are fakes intended to keep
users from getting too anxious.

Roy Harvey
Beacon Falls, CT

On 12 Sep 2006 02:21:12 -0700, nitinsharma717@.gmail.com wrote:

Quote:

Originally Posted by

>hi,
>
>In my code i have to increment progress bar based on percentage
>completion of Stored Procedure.I am not to get any solution on this.
>Please Help me on this issue.
>I am using win forms Visual studio 2005 and Sql server 2005.
>
>Thanx in advance
>
>Nitin

|||Roy Harvey wrote:

Quote:

Originally Posted by

Of course you could cheat and just pretend to show progress. I have
long been convinced that many progress bars are fakes intended to keep
users from getting too anxious.

In SQL Server you may or may not be correct. But other RDBMS products
do contain the ability to monitor progress, adjust for workload, and
accurately report back progress.

One example is Oracle's DBMS_APPLICATION_INFO built-in package
(http://www.psoug.org/reference/dbms..._info.html#aplo)
--
Daniel Morgan
University of Washington|||On Tue, 12 Sep 2006 06:24:41 -0700, DA Morgan <damorgan@.psoug.org>
wrote:

Quote:

Originally Posted by

>Roy Harvey wrote:
>

Quote:

Originally Posted by

>Of course you could cheat and just pretend to show progress. I have
>long been convinced that many progress bars are fakes intended to keep
>users from getting too anxious.

>
>In SQL Server you may or may not be correct. But other RDBMS products
>do contain the ability to monitor progress, adjust for workload, and
>accurately report back progress.
>
>One example is Oracle's DBMS_APPLICATION_INFO built-in package
>(http://www.psoug.org/reference/dbms..._info.html#aplo)

Interesting. Thanks for the enlightenment.

Roy

Find out if current user is member of a role

I need a stored procedure to find out if the current user is a member of a certain role.

I want to pass the role name and return a bit to tell whether he is a member or not.

I have this code to list the groups the current user is a member of but I don't know how to search or do a "SELECT FROM" on the results.

DECLARE @.usr varchar(32)

SET @.usr = USER

EXEC sp_helpuser @.usr

But if the current user is a member of more than one role it returns multiple rows. So if I could do something like:

DECLARE @.grpName varchar(32)

SELECT * FROM (EXEC sp_helpuser @.usr) WHERE GroupName=@.grpName
IF rowcount > 0 THEN
RETURN 1
ELSE
RETURN 0
END IF

I know that doesn't work so how can I do this?I'm sure that someone out there can do better than this, but you might try:

ALTER PROC spCheckGroup

@.UserName varchar(255), @.GroupName varchar(255)

AS

DECLARE @.Count int

SELECT @.Count = Count(*)
FROM (
select
s1.name as username,
s2.name as groupname
from
dbo.sysusers s1 left join dbo.sysmembers sm on
s1.uid = sm.memberuid
left outer join dbo.sysusers s2 on
sm.groupuid = s2.uid
where
s1.uid < 16383
) t1
WHERE
t1.userName = @.UserName and
t1.GroupName = @.GroupName

If @.Count > 0
Return 1
ELSE
Return 0

Test it with this code:

[/code]
DECLARE @.return_status int
EXEC @.return_status = spCheckGroup 'OAJO-SQLAdmin', 'db_owner'
SELECT 'Return Status' = @.return_status
[/code]|||check: BOL

IS_MEMBER ( { 'group' | 'role' } )|||I'll just crawl back under the rock where I came from...|||LOL.

Thanks for trying.|||I need a stored procedure to work with .NET so here's what I have:

CREATE PROCEDURE IsGroupMember
(
@.groupName nvarchar(32),
@.retVal bit OUTPUT
)
AS
SET @.retVal = IS_MEMBER(@.groupName)
GO

and in Query Analyzer I run this:

DECLARE @.bt bit

EXEC IsGroupMember 'db_owner', @.bt

IF @.bt = 1 print 'member'
ELSE IF @.bt = 0 print 'non-member'
ELSE print 'undefined'

but I keep getting undefined. What's wrong?|||EXEC IsGroupMember 'db_owner', @.bt output

Friday, March 9, 2012

find out active Sql statements

Hi,
Where does Sql server store the most current active T-SQL
statements? Is there a system table or stored procedure(documented or
undocumented)that will show you the active sql statements being
executed. For eg: in oracle you can query v$sql to see all the sql
statements executed by each session.
I know that you can use Enterprise manager, but was wondering if you
can run a query via query analyzer to look at it.
Any help is appreciated.

Thanks
Geetha<gdabbara@.brownshoe.com> wrote in message
news:1106264397.579113.143730@.c13g2000cwb.googlegr oups.com...
> Hi,
> Where does Sql server store the most current active T-SQL
> statements? Is there a system table or stored procedure(documented or
> undocumented)that will show you the active sql statements being
> executed. For eg: in oracle you can query v$sql to see all the sql
> statements executed by each session.
> I know that you can use Enterprise manager, but was wondering if you
> can run a query via query analyzer to look at it.
> Any help is appreciated.
> Thanks
> Geetha

Check out fn_get_sql() in Books Online - if it isn't there, you'll need to
download the latest BOL version from Microsoft:

http://www.microsoft.com/sql/techin.../2000/books.asp

DBCC INPUTBUFFER is another option, but it can only display the first 255
characters of whatever batch is being executed.

Simon|||[posted and mailed]

(gdabbara@.brownshoe.com) writes:
> Where does Sql server store the most current active T-SQL
> statements? Is there a system table or stored procedure(documented or
> undocumented)that will show you the active sql statements being
> executed. For eg: in oracle you can query v$sql to see all the sql
> statements executed by each session.
> I know that you can use Enterprise manager, but was wondering if you
> can run a query via query analyzer to look at it.

Simon pointed you to the basics. To get it all nicely packaged, I
have stored procedure aba_lockinfo that will give you the information.
Have a look at http://www.sommarskog.se/sqlutil/aba_lockinfo.html.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks you all for the information. It was very helpful.

Sunday, February 26, 2012

Find and Replace string value in a stored procedure.

Not sure if there is a way to do this programmatically. I am using SQL
Server 2005 and I need to find a specific string value in a stored
procedure and replace that string value with some other value.
I can perform the "Find" part of the process by querying the
Information_Schema.Routines view but not sure there is a way to
perform the replace, essentially ALTERING the stored procedure. Is
there a command I can use that can do this? I essentially want to do
a batch Find and Replace type process.
Thanks in advance!Safest way is to use the code files you have stored in your source code
control system. Check them all out, do a find/replace in files command
(using a tool such as Search and Replace), check them back in. Compile the
ones that had a replacement. S&R can provide a file listing that can be
used to drive a compile process using sqlcmd or numerous other mechanisms.
If you don't have a system like the above, take the time to set one up! :-)
You can easily script all sprocs to separate files using SSMS or many other
3rd party tools. I like ApexSQL's Script.
<lgalumbres@.gmail.com> wrote in message
news:1191591082.224636.49880@.o80g2000hse.googlegroups.com...
> Not sure if there is a way to do this programmatically. I am using SQL
> Server 2005 and I need to find a specific string value in a stored
> procedure and replace that string value with some other value.
> I can perform the "Find" part of the process by querying the
> Information_Schema.Routines view but not sure there is a way to
> perform the replace, essentially ALTERING the stored procedure. Is
> there a command I can use that can do this? I essentially want to do
> a batch Find and Replace type process.
> Thanks in advance!
>

Find all the objects(stored procedures) that uses a particular view

Hi,
I need to modify a View. But I don't know how many objects(stored procedure)
in SQL Server uses this view.
Is there a where to find out which objects use this particular view.
I can't do this manually because my DB contains 100's of stored procedures
Thanks
Kiranyou can check the dependancies in EM, or in query analyzer use sp_depends
sp_depends
Displays information about database object dependencies (for example, the
views and procedures that depend on a table or view, and the tables and
views that are depended on by the view or procedure). References to objects
outside the current database are not reported.
Syntax
sp_depends [ @.objname = ] 'object'
Arguments
[@.objname =] 'object'
The database object to examine for dependencies. The object can be a table,
view, stored procedure, or trigger. object is nvarchar(776), with no
default.
Return Code Values
0 (success) or 1 (failure)
"Kiran Kumar" <nospam@.spam.com> wrote in message
news:%23zq%23FG1HFHA.3500@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I need to modify a View. But I don't know how many objects(stored
procedure)
> in SQL Server uses this view.
> Is there a where to find out which objects use this particular view.
> I can't do this manually because my DB contains 100's of stored procedures
> Thanks
> Kiran
>|||Hi Simon,
The "sp_depends view_name" returns the list of column names that are in the
view and the related object name, type etc.....
I want to get the names of object that uses this View.
For eg. I have a view vew_employees. There are 4 stored procedures that uses
this view vew_employees.
I need something that will return me the names of these 4 sp.s
Thanks
Kiran
"Simon Worth" <REMOVEFIRST_simon.worth@.gmail.com> wrote in message
news:eiHfFM1HFHA.3376@.TK2MSFTNGP14.phx.gbl...
> you can check the dependancies in EM, or in query analyzer use sp_depends
> sp_depends
> Displays information about database object dependencies (for example, the
> views and procedures that depend on a table or view, and the tables and
> views that are depended on by the view or procedure). References to
> objects
> outside the current database are not reported.
> Syntax
> sp_depends [ @.objname = ] 'object'
> Arguments
> [@.objname =] 'object'
> The database object to examine for dependencies. The object can be a
> table,
> view, stored procedure, or trigger. object is nvarchar(776), with no
> default.
> Return Code Values
> 0 (success) or 1 (failure)
>
> "Kiran Kumar" <nospam@.spam.com> wrote in message
> news:%23zq%23FG1HFHA.3500@.TK2MSFTNGP14.phx.gbl...
>> Hi,
>> I need to modify a View. But I don't know how many objects(stored
> procedure)
>> in SQL Server uses this view.
>> Is there a where to find out which objects use this particular view.
>> I can't do this manually because my DB contains 100's of stored
>> procedures
>> Thanks
>> Kiran
>>
>|||Assuming your view name is somewhat unique, this will give you an idea:
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%view_name%'
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Kiran Kumar" <nospam@.spam.com> wrote in message
news:e4vvuU1HFHA.3332@.TK2MSFTNGP15.phx.gbl...
> Hi Simon,
> The "sp_depends view_name" returns the list of column names that are in
the
> view and the related object name, type etc.....
> I want to get the names of object that uses this View.
> For eg. I have a view vew_employees. There are 4 stored procedures that
uses
> this view vew_employees.
> I need something that will return me the names of these 4 sp.s
> Thanks
> Kiran
> "Simon Worth" <REMOVEFIRST_simon.worth@.gmail.com> wrote in message
> news:eiHfFM1HFHA.3376@.TK2MSFTNGP14.phx.gbl...
> > you can check the dependancies in EM, or in query analyzer use
sp_depends
> >
> > sp_depends
> > Displays information about database object dependencies (for example,
the
> > views and procedures that depend on a table or view, and the tables and
> > views that are depended on by the view or procedure). References to
> > objects
> > outside the current database are not reported.
> >
> > Syntax
> > sp_depends [ @.objname = ] 'object'
> >
> > Arguments
> > [@.objname =] 'object'
> >
> > The database object to examine for dependencies. The object can be a
> > table,
> > view, stored procedure, or trigger. object is nvarchar(776), with no
> > default.
> >
> > Return Code Values
> > 0 (success) or 1 (failure)
> >
> >
> >
> > "Kiran Kumar" <nospam@.spam.com> wrote in message
> > news:%23zq%23FG1HFHA.3500@.TK2MSFTNGP14.phx.gbl...
> >> Hi,
> >>
> >> I need to modify a View. But I don't know how many objects(stored
> > procedure)
> >> in SQL Server uses this view.
> >>
> >> Is there a where to find out which objects use this particular view.
> >>
> >> I can't do this manually because my DB contains 100's of stored
> >> procedures
> >>
> >> Thanks
> >> Kiran
> >>
> >>
> >
> >
>|||But it does return those 4 procs - within the name column of the output. Is
this not what you are looking for?
In enterprise manager, right click the view, go to "all tasks", and click
"display dependancies...". This will show you the same thing
"Kiran Kumar" <nospam@.spam.com> wrote in message
news:e4vvuU1HFHA.3332@.TK2MSFTNGP15.phx.gbl...
> Hi Simon,
> The "sp_depends view_name" returns the list of column names that are in
the
> view and the related object name, type etc.....
> I want to get the names of object that uses this View.
> For eg. I have a view vew_employees. There are 4 stored procedures that
uses
> this view vew_employees.
> I need something that will return me the names of these 4 sp.s
> Thanks
> Kiran
> "Simon Worth" <REMOVEFIRST_simon.worth@.gmail.com> wrote in message
> news:eiHfFM1HFHA.3376@.TK2MSFTNGP14.phx.gbl...
> > you can check the dependancies in EM, or in query analyzer use
sp_depends
> >
> > sp_depends
> > Displays information about database object dependencies (for example,
the
> > views and procedures that depend on a table or view, and the tables and
> > views that are depended on by the view or procedure). References to
> > objects
> > outside the current database are not reported.
> >
> > Syntax
> > sp_depends [ @.objname = ] 'object'
> >
> > Arguments
> > [@.objname =] 'object'
> >
> > The database object to examine for dependencies. The object can be a
> > table,
> > view, stored procedure, or trigger. object is nvarchar(776), with no
> > default.
> >
> > Return Code Values
> > 0 (success) or 1 (failure)
> >
> >
> >
> > "Kiran Kumar" <nospam@.spam.com> wrote in message
> > news:%23zq%23FG1HFHA.3500@.TK2MSFTNGP14.phx.gbl...
> >> Hi,
> >>
> >> I need to modify a View. But I don't know how many objects(stored
> > procedure)
> >> in SQL Server uses this view.
> >>
> >> Is there a where to find out which objects use this particular view.
> >>
> >> I can't do this manually because my DB contains 100's of stored
> >> procedures
> >>
> >> Thanks
> >> Kiran
> >>
> >>
> >
> >
>

Find all the objects(stored procedures) that uses a particular view

Find all read-only Databases using Stored Procedure

I am running a Stored Procedure that goes through all the databases and
reindexs them. However when it reaches a database that is "read
only" the program quits with an error. If there an easy way to
determine if the database is Read only and skip it if it is?
Using SQL 2000 / 2005
Thanks
-Matt-
DECLARE @.ExecSQLcmd varchar(1024)
DECLARE @.DBNum_to_Name int
SELECT @.DBNum_to_Name =min(dbid)
FROM master.dbo.sysdatabases
WHERE dbid > 4
WHILE @.DBNum_to_Name is not null
BEGIN
SELECT @.ExecSQLcmd='use ' + name + ' exec dbo.sp_UD_RebuildIndexes 30,
100'
FROM master.dbo.sysdatabases
WHERE dbid =@.DBNum_to_Name
EXEC (@.ExecSQLcmd)
SELECT @.DBNum_to_Name =min(dbid)
FROM master.dbo.sysdatabases
WHERE dbid > @.DBNum_to_Name and dbid != 9 and dbid != 25 -- Exclude
DBs here.
ENDAND DATABASEPROPERTY(name, 'IsReadOnly') = 0
<MKruer@.gmail.com> wrote in message
news:1138305254.208071.14400@.g14g2000cwa.googlegroups.com...
>I am running a Stored Procedure that goes through all the databases and
> reindexs them. However when it reaches a database that is "read
> only" the program quits with an error. If there an easy way to
> determine if the database is Read only and skip it if it is?
> Using SQL 2000 / 2005
> Thanks
> -Matt-
>
> DECLARE @.ExecSQLcmd varchar(1024)
> DECLARE @.DBNum_to_Name int
> SELECT @.DBNum_to_Name =min(dbid)
> FROM master.dbo.sysdatabases
> WHERE dbid > 4
> WHILE @.DBNum_to_Name is not null
> BEGIN
> SELECT @.ExecSQLcmd='use ' + name + ' exec dbo.sp_UD_RebuildIndexes 30,
> 100'
> FROM master.dbo.sysdatabases
> WHERE dbid =@.DBNum_to_Name
> EXEC (@.ExecSQLcmd)
> SELECT @.DBNum_to_Name =min(dbid)
> FROM master.dbo.sysdatabases
> WHERE dbid > @.DBNum_to_Name and dbid != 9 and dbid != 25 -- Exclude
> DBs here.
> END
>

Friday, February 24, 2012

Find / Search for a string in stored procedure?

Hi, I would like to find all the cases within all the stored procedures in a
database where a particular word exists.
Any suggestions? Thanks in advance.
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%YourWord%'
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"rh" <rherrera@.smci.com> wrote in message
news:OCKPvd1HFHA.2476@.TK2MSFTNGP12.phx.gbl...
> Hi, I would like to find all the cases within all the stored procedures in
a
> database where a particular word exists.
> Any suggestions? Thanks in advance.
>
|||Try,
use northwind
go
declare @.s varchar(25)
set @.s = 'orders'
select distinct
object_name([id])
from
syscomments
where
objectproperty([id], 'IsProcedure') = 1
and patindex('%' + @.s + '%', [text]) > 0
go
AMB
"rh" wrote:

> Hi, I would like to find all the cases within all the stored procedures in a
> database where a particular word exists.
> Any suggestions? Thanks in advance.
>
>
|||http://www.aspfaq.com/2037
http://www.aspfaq.com/
(Reverse address to reply.)
"rh" <rherrera@.smci.com> wrote in message
news:OCKPvd1HFHA.2476@.TK2MSFTNGP12.phx.gbl...
> Hi, I would like to find all the cases within all the stored procedures in
a
> database where a particular word exists.
> Any suggestions? Thanks in advance.
>
|||I discussed this at:
http://vyaskn.tripod.com/sql_server_...edure_code.htm
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"rh" <rherrera@.smci.com> wrote in message
news:OCKPvd1HFHA.2476@.TK2MSFTNGP12.phx.gbl...
> Hi, I would like to find all the cases within all the stored procedures in
a
> database where a particular word exists.
> Any suggestions? Thanks in advance.
>

Find / Search for a string in stored procedure?

Hi, I would like to find all the cases within all the stored procedures in a
database where a particular word exists.
Any suggestions? Thanks in advance.SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%YourWord%'
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"rh" <rherrera@.smci.com> wrote in message
news:OCKPvd1HFHA.2476@.TK2MSFTNGP12.phx.gbl...
> Hi, I would like to find all the cases within all the stored procedures in
a
> database where a particular word exists.
> Any suggestions? Thanks in advance.
>|||Try,
use northwind
go
declare @.s varchar(25)
set @.s = 'orders'
select distinct
object_name([id])
from
syscomments
where
objectproperty([id], 'IsProcedure') = 1
and patindex('%' + @.s + '%', [text]) > 0
go
AMB
"rh" wrote:

> Hi, I would like to find all the cases within all the stored procedures in
a
> database where a particular word exists.
> Any suggestions? Thanks in advance.
>
>|||http://www.aspfaq.com/2037
http://www.aspfaq.com/
(Reverse address to reply.)
"rh" <rherrera@.smci.com> wrote in message
news:OCKPvd1HFHA.2476@.TK2MSFTNGP12.phx.gbl...
> Hi, I would like to find all the cases within all the stored procedures in
a
> database where a particular word exists.
> Any suggestions? Thanks in advance.
>|||I discussed this at:
http://vyaskn.tripod.com/sql_server...cedure_code.htm
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"rh" <rherrera@.smci.com> wrote in message
news:OCKPvd1HFHA.2476@.TK2MSFTNGP12.phx.gbl...
> Hi, I would like to find all the cases within all the stored procedures in
a
> database where a particular word exists.
> Any suggestions? Thanks in advance.
>

Thursday, March 29, 2012

Finding latest full backup file name

Finding latest full backup file name

Finding latest full backup file name

Wednesday, March 28, 2012

Finding dependency of Objects in SQL Server 2000

Monday, March 26, 2012

Finding all references to a column

Finding affected tables

Friday, March 23, 2012

Find what procedure was execd

Wednesday, March 21, 2012

find type of a column

Find the Stored procedure

Monday, March 12, 2012

find right database for stored procedure

Find out the percentage complete of Stored Procedure

Find out if current user is member of a role

Friday, March 9, 2012

find out active Sql statements

Sunday, February 26, 2012

Find and Replace string value in a stored procedure.

Find all the objects(stored procedures) that uses a particular view

Find all the objects(stored procedures) that uses a particular view

Find all the objects(stored procedures) that uses a particular view

Find all read-only Databases using Stored Procedure

Friday, February 24, 2012

Find / Search for a string in stored procedure?

Find / Search for a string in stored procedure?

Fragmented array

Blog Archive

About Me