Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 701
  • Last Modified:

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?
0
KathrynS
Asked:
KathrynS
1 Solution
 
rafranciscoCommented:
You can try this:

CREATE PROCEDURE spWhichTable @ColumnName VARCHAR(50)
AS

SELECT A.[Name] AS [TableName]
FROM [dbo].[sysobjects] A INNER JOIN [dbo].[syscolumns] B
  ON A.[ID] = B.[ID] AND
     A.[XType] = 'U' AND
     B.[Name] = @ColumnName
0
 
obahatCommented:
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

0
 
Brian CroweCommented:
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
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
KathrynSAuthor Commented:
how would i execute this procedure?
0
 
rafranciscoCommented:
If you want to know which tables contain the ID column:

EXEC spWhichTable 'ID'
0
 
Scott PletcherSenior DBACommented:
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 )
0
 
PePiCommented:
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!


0
 
PePiCommented:
too late... LOL!!!
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Tackle projects and never again get stuck behind a technical roadblock.
Join Now