Check Dependencies For Stored Procedures

Dec 27, 2004

Hello, everyone:

I have 40 stored procedures in a database. A table X is called by some procedures. How to check which procedure call table X, and how many procedures call table X? Thanks

ZYT

View 1 Replies


ADVERTISEMENT

Check For Invalid Stored Procedures

Jan 16, 2009

Any code out there to check to see if stored procedures, views, etc. are invalidated (as with Oracle that shows procedure as being INVALID) due to dependent object changes ?

View 9 Replies View Related

Utilities To Check For Errors In Stored Procedures

Jul 20, 2005

I have written a utility that checks our stored procedures forpotentially incorrect code (eg = null as opposed to is null, unusedvariables/parameters etc). It also ensures that parameters/variablesetc match our naming conventions and data types/sizes are the sames asthe corresponding type in the database (if appropriate)Are there any other utilities that do this sort of thing?

View 1 Replies View Related

How To Write Query For Add Dependencies And Remove Dependencies?

Dec 1, 2007

 I need to write a code for remove dependencies between Table1.Prikey and Table2.Idand  add dependencies between Table1.Prikey and Table3.Id how to write the code? please help .. thank you very much 

View 2 Replies View Related

How To Get All The Dependencies Of A Stored Procedure?

Mar 5, 2008

Hi All, How to get all the Dependencies of a Stored Procedure?  Dependencies may exists in other databases also. For Example Stored procedure "sp_Employee" exists in the DB "Employee" This SP calling the another SP exists in another DB "Organization" You help would be appreciated.. RegardsGokul 

View 2 Replies View Related

Where Are Assembly Dependencies (on SQL Objects) Stored?

Oct 4, 2007



I've created an assembly in Adventureworks, and created a sproc that references a function inside the assembly. All works well with the asssembly and proc.


I know that I can't drop the assembly without first dropping the sproc, but I can't find where the dependency information is stored. sp_depends, sys.sql_dependencies, etc, all come up empty for the assembly, and when I run sp_depends on the SQL sproc object, I get:

"Object does not reference any object, and no objects reference it."


Clearly that's not the case, since if I attempt to drop the assembly, I get the expected error 6590: "DROP ASSEMBLY failed because 'ProcTest' is referenced by object 'uspGetStuffFromAssembly' "


Any idea where this is being stored?

--
Chris Randall
cfrandall@google'smail.com
blogs.ameriteach.com/chris-randall

View 5 Replies View Related

Oracle Stored Procedures VERSUS SQL Server Stored Procedures

Jul 23, 2005

I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!

View 11 Replies View Related

Stored Procedures 2005 Vs Stored Procedures 2000

Sep 30, 2006

Hi,



This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.



Thank you in advance for any help on this matter



View 1 Replies View Related

All My Stored Procedures Are Getting Created As System Procedures!

Nov 6, 2007



Using SQL 2005, SP2. All of a sudden, whenever I create any stored procedures in the master database, they get created as system stored procedures. Doesn't matter what I name them, and what they do.

For example, even this simple little guy:

CREATE PROCEDURE BOB

AS

PRINT 'BOB'

GO

Gets created as a system stored procedure.

Any ideas what would cause that and/or how to fix it?

Thanks,
Jason

View 16 Replies View Related

How To Search And List All Stored Procs In My Database. I Can Do This For Tables, But Need To Figure Out How To Do It For Stored Procedures

Apr 29, 2008

How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out?
 SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

View 1 Replies View Related

Using A Stored Procedure To Query Other Stored Procedures And Then Return The Results

Jun 13, 2007

Seems like I'm stealing all the threads here, : But I need to learn :) I have a StoredProcedure that needs to return values that other StoredProcedures return.Rather than have my DataAccess layer access the DB multiple times, I would like to call One stored Procedure, and have that stored procedure call the others to get the information I need. I think this way would be more efficient than accessing the DB  multiple times. One of my SP is:SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived,     I.Expired, I.ExpireDate, I.Deleted, S.Name AS 'StatusName', S.ItemDetailStatusID,    S.InProgress as 'StatusInProgress', S.Color AS 'StatusColor',T.[Name] AS 'TypeName',    T.Prefix, T.Name AS 'ItemDetailTypeName', T.ItemDetailTypeID    FROM [Item].ItemDetails I    INNER JOIN Item.ItemDetailStatus S ON I.ItemDetailStatusID = S.ItemDetailStatusID    INNER JOIN [Item].ItemDetailTypes T ON I.ItemDetailTypeID = T.ItemDetailTypeID However, I already have StoredProcedures that return the exact same data from the ItemDetailStatus table and ItemDetailTypes table.Would it be better to do it above, and have more code to change when a new column/field is added, or more checks, or do something like:(This is not propper SQL) SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived,     I.Expired, I.ExpireDate, I.Deleted, EXEC [Item].ItemDetailStatusInfo I.ItemDetailStatusID, EXEC [Item].ItemDetailTypeInfo I.ItemDetailTypeID    FROM [Item].ItemDetails IOr something like that... Any thoughts? 

View 3 Replies View Related

How To Save Stored Procedure To NON System Stored Procedures - Or My Database

May 13, 2008

Greetings:

I have MSSQL 2005. On earlier versions of MSSQL saving a stored procedure wasn't a confusing action. However, every time I try to save my completed stored procedure (parsed successfully ) I'm prompted to save it as a query on the hard drive.

How do I cause the 'Save' action to add the new stored procedure to my database's list of stored procedures?

Thanks!

View 5 Replies View Related

Stored Procedure Being Saved In System Stored Procedures

Apr 7, 2006

We recently upgraded to SQL Server 2005. We had several stored procedures in the master database and, rather than completely rewriting a lot of code, we just recreated these stored procedures in the new master database.

For some reason, some of these stored procedures are getting stored as "System Stored Procedures" rather than just as "Stored Procedures". Queries to sys.Objects and sys.Procedures shows that these procs are being saved with the is_ms_shipped field set to 1, even though they obviously were not shipped with the product.

I can't update the sys.Objects or sys.Procedures views in 2005.

What effect will this flag (is_ms_shipped = 1) have on my stored procedures?

Can I move these out of "System Stored Procedures" and into "Stored Procedures"?

Thanks!

View 24 Replies View Related

How Can I Call One Or More Stored Procedures Into Perticular One Stored Proc ?

Apr 23, 2008

Hello friends......How are you ? I want to ask you all that how can I do the following ?
I want to now that how many ways are there to do this ?



How can I call one or more stored procedures into perticular one Stored Proc ? in MS SQL Server 2000/05.

View 1 Replies View Related

SSIS And Stored Procedures Results Stored In #Tables

Mar 26, 2008

Hello
I'm start to work with SSIS.

We have a lot (many hundreds) of old (SQL Server2000) procedures on SQL 2005.
Most of the Stored Procedures ends with the following commands:


SET @SQLSTRING = 'SELECT * INTO ' + @OutputTableName + ' FROM #RESULTTABLE'

EXEC @RETVAL = sp_executeSQL @SQLSTRING


How can I use SSIS to move the complete #RESULTTABLE to Excel or to a Flat File? (e.g. as a *.csv -File)

I found a way but I think i'ts only a workaround:

1. Write the #Resulttable to DB (changed Prozedure)
2. create data flow task (ole DB Source - Data Conversion - Excel Destination)

Does anyone know a better way to transfer the #RESULTTABLE to Excel or Flat file?

Thanks for an early Answer
Chaepp

View 9 Replies View Related

MS SQL Stored Procedures Inside Another Stored Procedure

Jun 16, 2007

Hi,
 Do you know how to write stored procedures inside another stored procedure in MS SQL.
 
Create procedure spMyProc inputData varchar(50)
AS
 ----- some logical
 
 procedure spMyProc inputInsideData varchar(10)
AS
   --- some logical
  ---  go
-------

View 5 Replies View Related

Calling Stored Procedures From Another Stored Procedure

May 8, 2008

I am writing a set of store procedures (around 30), most of them require the same basic logic to get an ID, I was thinking to add this logic into an stored procedure.

The question is: Would calling an stored procedure from within an stored procedure affect performance? I mean, would it need to create a separate db connection? am I better off copying and pasting the logic into all the store procedures (in terms of performance)?

Thanks in advance

John

View 5 Replies View Related

Calling A Stored Procedure Inside Another Stored Procedure (or Nested Stored Procedures)

Nov 1, 2007

Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly.  For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie
exec dbo.DeriveStatusID 'Created'
returns an int value as 1
(performed by "SELECT statusID FROM statusList WHERE statusName= 'Created') 
but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie:
exec dbo.AddProduct_Insert 'widget1'
which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
I want to simply the insert to perform (in one sproc):
SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example).
My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert). 
Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
 

View 1 Replies View Related

How To Check Whether Stored Procedure Is Being Used

Apr 14, 2008

Hi,

I'm using some security scanning software which looks for vulnerabilities in the database. It tells me that some stored procedure are should not be given public permission.
How do i know whether the stored procedures are being used by someone or last used on which date? Is there any way to find out?

At the same time, how do i check the permission of stored procedure on ms sql 2005? Thanks

View 3 Replies View Related

How To Check Stored Procedure?

May 2, 2004

Hello, everyone:

I have some stored procedures that run on SQL2K(SP3) /WIN2K. Sometimes I modify them using ALTER PROCEDURE statements. How to check if they are changed after ALTER statements run? Thanks.

ZYT

View 4 Replies View Related

Stored Procedure To Check Different Criterias

Aug 6, 2007

I've a stored procedure which retrieves based on different criterias. I added one more critieria - to display a column based on a range of values. The values are @OriginalMin and @OriginalMax. I declared the variables and gave the conditions. But still set ANSI_NULLS ON
set QUOTED_IDENTIFIER OFF
GO
ALTER procedure [dbo].[USP_Account_Search_Mod]@OriginalMin DECIMAL=0
,@OriginalMax DECIMAL=0
AS
DECLARE
@CRI8 VARCHAR(500)SELECT
@CRI1=''
SET @CRI8='AND OriginalBalance >=@OriginalMin AND OriginalBalance<=@OriginalMax'
SELECT @Criteria = ......+ @CRI8
When I execute this stored procedure, I get the following error message.
SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(3)) AND Customer = '00001'AND OriginalBalance >=@OriginalMin AND OriginalBalance<=@OriginalMax UNION SELECT * FROM dbo.UDV_Tier2Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(3)) AND Customer = '00001'AND OriginalBalance >=@OriginalMin AND OriginalBalance<=@OriginalMaxORDER BY NAME ASC
Msg 137, Level 15, State 2, Line 1
Must declare the variable '@OriginalMin'.
Msg 137, Level 15, State 2, Line 1
Must declare the variable '@OriginalMin
Could someone tell what's wrong with the procedure? For convenience, I've included only the latest critieria I added.

View 2 Replies View Related

How To Check For Nulls With-in A Stored Procedure

May 24, 2008

I have a Stored Procedure as followsUSE [MyDataBase]
GO
/****** Object: StoredProcedure [dbo].[SPLogins] Script Date: 05/24/2008 21:58:50 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
Create  PROCEDURE [dbo].[SPLogins]
(
@LoginName varchar(50), @LoginD int output,
)
AS
Select @LoginID = LoginID From MyDatabase Where (LoginName Is not Null) and @LoginName = LoginName
 
I am trying to check for a null value in the Stored Procedure.  I dont' get an error, but it doesn't catch the "null" and gives an error when there is a null value
What Is the correct wayt to go about it.

View 3 Replies View Related

How To Check Stored Procedure Status

Dec 18, 2003

How to check status(valid/invalid) for all stored procedure in my DB...

View 14 Replies View Related

Check If A Stored Procedure Exists

Mar 14, 2007

Hi All.

Can anyone tell me what the syntax to check and see if a stored procedure exists and if it doesnt to create it is?

Thanks people.

View 8 Replies View Related

Only Functions And Extended Stored Procedures Can Be Executed From Within A Function. Sp_executesql Is A Extended Stored Prod

May 15, 2008

i have created the folowing function but keep geting an error.

Only functions and extended stored procedures can be executed from within a function.

Why am i getting this error!

Create Function myDateAdd
(@buildd nvarchar(4), @avdate as nvarchar(25))
Returns nvarchar(25)
as
Begin
declare @ret nvarchar(25)
declare @sqlval as nvarchar(3000)

set @sqlval = 'select ''@ret'' = max(realday) from (
select top '+ @buildd +' realday from v_caltable where realday >= '''+ @avdate +''' and prod = 1 )a'

execute sp_executesql @sqlval
return @ret
end

View 3 Replies View Related

Stored Procedure To Check Date Range

Nov 10, 2006

Hi guys,I have written a stored procedure to check for date range, say if the user enters a value for 'city-from' , 'city-to', 'start-date' and end-date, this stored procedure should verify these 2 dates against the dates stored in the database. If these 2 dates had already existed for the cities that they input, the stored procedure should return 1 for the PIsExists parameter. Below's how I constructed the queries:  1 ALTER PROCEDURE dbo.DateCheck
2 @PID INTEGER = -1 OUTPUT,
3 @PCityFrom Char(3) = '',
4 @PCityTo Char(3) = '',
5 @PDateFrom DATETIME = '31 Dec 9999',
6 @PDateTo DATETIME = '31 Dec 9999',
7 @PIsExists BIT = 1 OUTPUT
8 AS
9
10 CREATE TABLE #TmpControlRequst
11 (
12 IDINTEGER,
13 IsExistsCHAR(1)
14 )
15 /*###Pseudo
16 1. Check the Date From and Date To
17 -- select all the value equal to parameter cityFrom and cityTo
18 -- insert the selection records into tmp table
19 --*/
20 INSERT INTO #TmpControlRequst
21 (ID, IsExists)
22 SELECT ID,
23 IsExists = CASE WHEN DateFrom <> '31 Dec 9999' AND DateTo <> '31 Dec 9999'
24 AND @PDateFrom <= DateFrom AND @PDateFrom <= DateTo
25 AND @PDateTo >= DateFrom AND @PDateTo <= DateTo THEN 1
26 WHEN DateFrom <> '31 Dec 9999' AND DateTo <> '31 Dec 9999'
27 AND @PDateFrom >= DateFrom AND @PDateFrom <= DateTo
28 AND @PDateTo >= DateFrom AND @PDateTo <= DateTo THEN 1
29 WHEN DateFrom <> '31 Dec 9999' AND DateTo <> '31 Dec 9999'
30 AND @PDateFrom >= DateFrom AND @PDateFrom <= DateTo
31 AND @PDateTo >= DateFrom AND @PDateTo >= DateTo THEN 1
32 WHEN DateFrom <> '31 Dec 9999' AND DateTo <> '31 Dec 9999'
33 AND @PDateFrom <= DateFrom AND @PDateFrom <= DateTo
34 AND @PDateTo >= DateFrom AND @PDateTo >= DateTo THEN 1
35 ELSE 0 END
36 FROM RequestTable
37 WHERE ID <> @PID
38 AND CityFrom = @PCityFrom
39 AND CityTo = @PCityTo
40
41 --======== FINAL RESULT
42 -- For tmp table:-
43 -- isExists = 1 ==> date lapse
44 -- isExists = 0 ==> date ok
45 -- if count for (isExists = 1) in tmp table is > 0 then return 1 and data not allow for posting
46 SELECT @PIsExists = CASE WHEN COUNT(*) > 0 THEN 1
47 ELSE 0 END
48 FROM #TmpControlRequst
49 WHEREIsExists = 1
50
51 SELECT @PIsExists
52 --=========
53
54 DROP TABLE #TmpControlRequst
55
56 --=========
57 RETURN(0)However, when I run this stored procedure, 'PIsExists' would always return -1. I am positive that the values that I passed in, had already existed in the database. Any idea what might be causing this problem? Thanks in advance

View 6 Replies View Related

How To Check If An Insert Stored Proc Succeeded

Mar 5, 2007

Hi,
How would I check if an insert via stored proc succeeded? Here's the proc I'm using:set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[showtube_addNewSUser]
-- Add the parameters for the stored procedure here
@UserId uniqueidentifier,
@FirstName nvarchar(32),
@LastName nvarchar(32),
@DescShort nvarchar(256),
@DescLong ntext,
@ClanID uniqueidentifier
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
Insert into dbo.Users (UserId, FirstName, LastName, DescShort, DescLong, ClanID)
values (@UserId, @FirstName, @LastName, @DescShort, @DescLong, @ClanID);
END


 
 
I tried adding a Return @@RowCount before the END statement, but it always seems to return -1. Could someone tell me what I'm doing wrong? Thanks.

View 3 Replies View Related

Select And Check A Boolean Value With Stored Procedure?

Apr 10, 2008

 Hi all,How do you select and check a boolean value within a stored procedure? I don't know if this is correct or not:DECLARE @CheckStatus bitSELECT @CheckStatus = PREF_STATUS FROM  tblPrefsWHERE [PREF_ID] = @PREF_IDIF (@CheckStatus IS FALSE) -- DO SOMETHINGIF (@CheckStatus IS TRUE) -- DO SOMETHING But I'm not sure if what I've used (IS FALSE/TRUE) is the correct way? "PREF_STATUS" is a bit column in my table.Thanks. 

View 4 Replies View Related

SQL 2012 :: Check If Particular Stored Procedure Is Recompiling Or Not

Mar 23, 2014

I want to know, Is it possible to check a particular stored procedure is recompiling or not?

View 7 Replies View Related

How To Check When A Stored Procedure Was Last Called/executed

Jul 23, 2005

HiOur SQL server has a lot of stored procedures and we want to get somecleaning up to be done. We want to delete the ones that have been notrun for like 2-3 months. How exactly will i find out which ones todelete. Enterprise manager only seesm to give the "Create Date"How exactly can I find the last called date ! I guess you could write aquery for that ! but how ???P.S I dont want to run a trace for 1 months and see what storedprocedures are not being used.

View 7 Replies View Related

Query - Check For A String In Stored Procedure

Jul 23, 2005

Hi,I would like to check if a string value exist in a string in sqlserver stored procedure, e.g.set @testString = 'this is my test document.'if (@testString contains 'test')begin.....endHow do I do this in sql server stored procedure?Thanks,June...

View 1 Replies View Related

Need A SqlServer Stored Procedur To Check Login Credentials

Mar 10, 2006

Sid writes "Hi,
can anybody plz provide me the stored procedure for checking the credentials(uid,Pwd) of a login page in sqlserver.

Thanks,
Sid."

View 2 Replies View Related

Stored Procedures

Jun 17, 2006

hi

i need to use only one stored procedure and access many tablesso how write a stored procedure for that dohelp me looking forward for a reply to the earliest i am developing web page using asp.net using c# and sqlserver as backend

looking forward for a replygayathri

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved