Table Identification - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Table Identification

Author

Topic

AMAleela
Starting Member

8 Posts

Posted - 2013-08-07 : 11:16:48

Hi ,

I have a table which is referenced in multiple stored procedures .Among them only one procedure loads data (INSERT statement) to the table. In all other procedures table is referenced in FROM clause.

My question is how do i find the stored procedure that is actually loading data into that table.Please help .

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-07 : 11:27:58

This is not a perfect method, but it should help you narrow down the list of candidate stored procedures. Once you have this list, look through each to see which one of them is doing the insert

SELECT  *
FROM    sys.objects
WHERE   OBJECT_DEFINITION(object_id) LIKE '%YourTableNameHere'
        AND OBJECT_DEFINITION(object_id) LIKE '%INSERT%';

AMAleela
Starting Member

8 Posts

Posted - 2013-08-07 : 11:39:46

Hi , thank you for responding back.But this does not give anything stored procedure list , it just gives table information

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-07 : 11:44:36

Ah! Try

SELECT  OBJECT_NAME(OBJECT_ID) As StoredPrcoName,  *
FROM    sys.objects
WHERE   OBJECT_DEFINITION(object_id) LIKE '%YourTableNameHere%'
        AND OBJECT_DEFINITION(object_id) LIKE '%INSERT%';

Now the first column should be the name of the stored proc.

AMAleela
Starting Member

8 Posts

Posted - 2013-08-07 : 11:46:49

There is no column name like OBJETCDEFINITION

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-07 : 11:46:50

Actually, it was the percent sign at the end that I missed. You shouldn't have to do the "OBJECT_NAME(OBJECT_ID) As StoredPrcoName," part, but it doesn't do any harm.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-07 : 11:48:15

quote:
Originally posted by AMAleela

There is no column name like OBJETCDEFINITION

Can you post the error message you are getting when you run that code? Didn't quite follow what you meant by the above. Is that an error message?

AMAleela
Starting Member

8 Posts

Posted - 2013-08-07 : 11:50:07

Sorry for miscommunication..

sys.objects does not have any column name like OBJECT_DEFINITION .

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-07 : 11:53:17

quote:
Originally posted by AMAleela

Sorry for miscommunication..

sys.objects does not have any column name like OBJECT_DEFINITION .

Are you using Microsoft SQL Server? OBJECT_DEFINITION is not a column name. It is a system function which takes OBJECT_ID as the parameter and returns the definition of the object.

Can you post the EXACT error message, error codes and all?

AMAleela
Starting Member

8 Posts

Posted - 2013-08-07 : 11:59:13

Yes , I am using Microsoft SQl server 2008 . When i execute this piece of code , it is not returning any rows. I am sure i have that table in the DB.

SELECT OBJECT_NAME(object_id) As StoredPrcoName, *
FROM sys.objects
WHERE OBJECT_DEFINITION(object_id) LIKE'address'
AND OBJECT_DEFINITION(object_id) LIKE '%INSERT%'

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-07 : 12:02:55

You need two percentage signs - see in red:

SELECT OBJECT_NAME(object_id) As StoredPrcoName, *
FROM sys.objects
WHERE OBJECT_DEFINITION(object_id) LIKE'%address%'
AND OBJECT_DEFINITION(object_id) LIKE '%INSERT%'

AMAleela
Starting Member

8 Posts

Posted - 2013-08-07 : 12:09:11

This is not giving me the list of procs where it is being loaded . Instead it gives me some subset of procs where it is referenced.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-07 : 12:14:32

That would mean that the data is not being inserted via a stored procedure. There are numerous other ways data could get into a table in a database:

It could be an SSIS job, it could be a BCP insert, or it could be being inserted from another database or a remote server (if there are linked servers). It may be inserted via view, or perhaps someone is inserting the data from ad-hoc code in a client program. It may also be that the stored procedure is encrypted.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-07 : 13:03:09

Forgive me, but if data is loaded into the table, doesn't it need to be referenced with an "INSERT INTO " in one of the procs?

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-07 : 13:17:02

That is what I would have thought too.

Since that doesn't seem to be the case, I am suspecting that the data is getting into the table some other way, for example, via ad-hoc insert statements in a C# program (or others that I listed in my previous post).

AMAleela
Starting Member

8 Posts

Posted - 2013-08-07 : 13:35:34

, I have written a stored procedure with an INSERT INTO statement to load data into that table.Rest of the procs have a reference to that table (FROM Clause).

There is only one stored procedure that populates data to that table when i execute that proc.Please advice.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-07 : 13:41:51

quote:
Originally posted by AMAleela

, I have written a stored procedure with an INSERT INTO statement to load data into that table.Rest of the procs have a reference to that table (FROM Clause).

There is only one stored procedure that populates data to that table when i execute that proc.Please advice.

Had that been the case, and you were querying against the correct database on the correct server, the query I posted earlier should have listed that stored procedure.

If your database has case-sensitive collation, just make sure that you use the correct case for the table name.

Do an experiment for yourself - you can copy this code and run it. It creates a test stored procedure, and then tries to find it. The only thing that select is doing is to look for any stored proc that has the word insert and the word someTableName in its definition.

-- create a test stored procedure.
CREATE PROCEDURE dbo.MyTest
AS
INSERT INTO [someTableName] VALUES (1,2,3);
GO

-- Find that stored proc.
SELECT OBJECT_NAME(object_id) As StoredPrcoName, *
FROM sys.objects
WHERE OBJECT_DEFINITION(object_id) LIKE'%someTableName%'
AND OBJECT_DEFINITION(object_id) LIKE '%INSERT%'
GO

-- cleanup
DROP PROCEDURE dbo.MyTest
GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-07 : 13:43:30

If you only want to "find" the procedure, click to the left in SSMS.
Expand you database, expand Programmability and then expand Stored Procedures.

That will give you all stored procedures in your database.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

AMAleela
Starting Member

8 Posts

Posted - 2013-08-07 : 13:59:52

I was able to find the issue , not the solution, lower case (insert into ) was used in the proc , if I change it to upper case (INSERT INTO) then i am seeing right results , But i have almost 300 procs with lower/upper case mixed insert into statements ..can you suggest a work around for this.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-07 : 15:25:36

quote:
Originally posted by AMAleela

I was able to find the issue , not the solution, lower case (insert into ) was used in the proc , if I change it to upper case (INSERT INTO) then i am seeing right results , But i have almost 300 procs with lower/upper case mixed insert into statements ..can you suggest a work around for this.

Force a case-insensitive collation like this:

SELECT OBJECT_NAME(object_id) As StoredPrcoName, *
FROM sys.objects
WHERE OBJECT_DEFINITION(object_id) COLLATE SQL_Latin1_General_CP1_CI_AS  
	LIKE'%SomeTableName%' COLLATE SQL_Latin1_General_CP1_CI_AS
AND OBJECT_DEFINITION(object_id) COLLATE SQL_Latin1_General_CP1_CI_AS 
	LIKE '%INSERT%' COLLATE SQL_Latin1_General_CP1_CI_AS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources