SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


stored procedure---finding all tables that contain a specific column


stored procedure---finding all tables that contain a specific column

Author
Message
t-pinto
t-pinto
SSC Veteran
SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)

Group: General Forum Members
Points: 241 Visits: 51
Hello, I am working on a stored procedure that will accept a column name and returns the name of the table or tables that have the column by that name. I am also coding a statement that calls the procedure. \

Below is what I have so far but when I call the procedure I get the following error when using the column name VendorID:
Incorrect syntax near 'VendorID'.

--Creating stored procedure called sp_WhichTable
use AP
GO

CREATE PROC sp_WhichTable
AS

SELECT o.name as [Table], c.name as [Column]
FROM SYSOBJECTS o JOIN SYSCOLUMNS c ON o.id = c.id
WHERE c.name like '%order%' AND o.xtype = 'u'
ORDER BY 1


--Calling the stored procedure
Declare @ColumnName varchar
exec sp_WhichTable @ColumnName = 'VendorID'
GilaMonster
GilaMonster
SSC Guru
SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)

Group: General Forum Members
Points: 148879 Visits: 45880
Well, your procedure (as you posted it) doesn't have any input parameters, hence trying to pass one is going to give an error.

You don't need a variable called @ColumnName, you need to change the procedure so that it accepts an input parameter, of type varchar, called @ColumnName

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


t-pinto
t-pinto
SSC Veteran
SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)

Group: General Forum Members
Points: 241 Visits: 51
ok, I added an imput parameter and removed the variable. I am still getting this error message when calling the procedure- Incorrect syntax near 'VendorID'.

CREATE PROC sp_WhichTable
@ColumnName varchar(50)
AS

SELECT o.name as [Table], c.name as [Column]
FROM SYSOBJECTS o JOIN SYSCOLUMNS c ON o.id = c.id
WHERE c.name like '%order%' AND o.xtype = 'u'
ORDER BY 1


exec sp_WhichTable @ColumnName 'VendorID'
GilaMonster
GilaMonster
SSC Guru
SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)

Group: General Forum Members
Points: 148879 Visits: 45880
You left out the equals sign in the procedure call. You had that part right last time.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


t-pinto
t-pinto
SSC Veteran
SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)

Group: General Forum Members
Points: 241 Visits: 51
--The procedure was created ok:

CREATE PROC sp_WhichTable
@ColumnName varchar(50)
AS

SELECT o.name as [Table], c.name as [Column]
FROM SYSOBJECTS o JOIN SYSCOLUMNS c ON o.id = c.id
WHERE c.name like '%order%' AND o.xtype = 'u'
ORDER BY 1

--When I execute the sp :

exec sp_WhichTable @ColumnName = 'invoiceID'

I get 2 blank columns with the column headers TABLE and COLUMN. I expected to get some results.
Looking at the tables I see that the column InvoiceID is in the InvoicesTable and InvoicesLineItems Tables.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)

Group: General Forum Members
Points: 148879 Visits: 45880
You're passing the value invoiceid to the procedure, but you're doing nothing with it within the procedure.

If you want to use the parameter to filter the query, it needs to be referenced within the query somehow. Perhaps in place of the hardcoded 'order'?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


GilaMonster
GilaMonster
SSC Guru
SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)

Group: General Forum Members
Points: 148879 Visits: 45880
Two more things.

Stored procedures should not be created with the sp_ name, That's for system procedures and means that SQL will look in one of the system databases first for the proc.

Since you're on 2005, use sys.tables and sys.columns instead of sysobjects and syscolumns. They are just there for backward compatibility and will be removed in a future version.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


t-pinto
t-pinto
SSC Veteran
SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)

Group: General Forum Members
Points: 241 Visits: 51
--The procedure was created ok:

CREATE PROC sp_WhichTable
@ColumnName varchar(50)
AS

SELECT o.name as [Table], c.name as [Column]
FROM SYSOBJECTS o JOIN SYSCOLUMNS c ON o.id = c.id
WHERE c.name like '%order%' AND o.xtype = 'u'
ORDER BY 1

--When I execute the sp :

exec sp_WhichTable @ColumnName = 'invoiceID'

I get 2 blank columns with the column headers TABLE and COLUMN. I expected to get some results.
Looking at the tables I see that the column InvoiceID is in the InvoicesTable and InvoicesLineItems Tables.
t-pinto
t-pinto
SSC Veteran
SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)

Group: General Forum Members
Points: 241 Visits: 51
I am now referencing the parameter in the stored procedure as follows:

use master
GO

CREATE PROC spWhichTable
@ColumnName varchar(50)
AS

SELECT o.name as [Table], c.name as [Column]
FROM sys.tables o JOIN sys.columns c ON o.id = c.id
WHERE c.name = '@ColumnName' AND o.xtype = 'u'

I am receiving these errors:

Msg 207, Level 16, State 1, Procedure spWhichTable, Line 7
Invalid column name 'id'.
Msg 207, Level 16, State 1, Procedure spWhichTable, Line 7
Invalid column name 'id'.
Msg 207, Level 16, State 1, Procedure spWhichTable, Line 8
Invalid column name 'xtype'.
RBarryYoung
RBarryYoung
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24106 Visits: 9518
try it like this:

CREATE PROC spWhichTable
@ColumnName varchar(50)
AS

SELECT o.name as [Table]
, c.name as [Column]
FROM sys.tables o
JOIN sys.columns c ON o.object_id = c.object_id
WHERE c.name = '@ColumnName'
AND o.type = 'U'



-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search