asked on

STORED PROCEDURE ACCEPT COLUMN NAME AND RETURN NAME OF TABLE

I need to create a stored procedure called spWhichTable that accepts a column name and returns the name of the table or tables that have a column by that name.
How would I go about doing this?
This is what I have so far but I'm not sure if it is right or not

USE AP
GO
CREATE PROC spWhichTable
@columnName CHAR,
@tableName CHAR
AS
SELECT @columnName
RETURN @tableName

Can someone please help me?

ASKER CERTIFIED SOLUTION

rafrancisco

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

obahat

The following code will return a result set of the tables that have that column

USE AP
GO
CREATE PROC spWhichTable
@columnName NVARCHAR(128)
AS

SELECT a.TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES a
INNER JOIN INFORMATION_SCHEMA.COLUMNS b
ON a.TABLE_NAME = b.TABLE_NAME
WHERE b.COLUMN_NAME = @ColumnName
and a.TABLE_TYPE = 'BASE TABLE'

GO

Brian Crowe

CREATE PROCEDURE spWhichTable
@columnName varchar(50)
AS

SELECT name
FROM sysobjects
INNER JOIN syscolumns
ON sysobjects.id = syscolumns.id
WHERE syscolumns.name = @columnName
AND sysobjects.xtype = 'U' -- for a user-created table
GO

KathrynS

ASKER

how would i execute this procedure?

rafrancisco

If you want to know which tables contain the ID column:

EXEC spWhichTable 'ID'

Scott Pletcher

Those will produce duplicate results. You could add DISTINCT, but an approach like below is probably better.

CREATE PROCEDURE spWhichTables
@columnName VARCHAR(40),
@tableNames VARCHAR(200) = NULL OUTPUT
AS
SET @tableNames = NULL
SELECT @tableNames = ISNULL(@tableNames + ',', '') + obj.name
FROM sysobjects obj WITH (NOLOCK)
WHERE obj.xtype = 'U' AND obj.name NOT LIKE 'dt%'
AND obj.id IN (
      SELECT id
      FROM syscolumns col WITH (NOLOCK)
      WHERE name = @columnName )
SET @tableNames = @tableNames

That code will return a list of table names in one variable. If you want a result set, naturally do this:

CREATE PROCEDURE spWhichTables
@columnName VARCHAR(40)
AS
SELECT obj.name
FROM sysobjects obj WITH (NOLOCK)
WHERE obj.xtype = 'U' AND obj.name NOT LIKE 'dt%'
AND obj.id IN (
      SELECT id
      FROM syscolumns col WITH (NOLOCK)
      WHERE name = @columnName )

PePi

CREATE PROC spWhichTable
@columnName nvarchar(50)
AS

SELECT o.name AS TableName, c.name AS ColumnName
FROM syscolumns c LEFT OUTER JOIN
sysobjects o ON o.id = c.id
WHERE c.name = @columnName AND o.type = 'U'

I suggest that your stored proc returns a recordset in case the column exists in more than 1 table

HTH!

PePi

too late... LOL!!!