Display TABLE_NAME from database

Hi,

I would like to display the TABLE_NAME variable where the table.ID =@.ID

So far I got here:

SELECT TABLE_NAMEFROM INFORMATION_SCHEMA.TABLESWHERE (TABLE_TYPE ='BASE TABLE')AND (TABLE_NAME.ID = @.ID)

but the last part AND (TABLE_NAME.ID = @.ID) does not work.

Thanks,

dg

Where do you get the ID from? There is no TABLE_NAME.ID column in the view.

You could do

SELECT [Name]

FROM sysobjects

WHERE Id = @.id

|||Every table has an ID column so, I would like to display only the names of those tables where column ID is @.ID|||

I understand. For the proc to run and return the table name, you are passing the value from your application right? How are you getting the ID of the object to the application?

And there is only 1 table/object for a given ID. So if you can explain more as to what information you have and what you are trying to retrieve we can help you better.

|||This is what I have

Table1Table2Table3Table4IDEntryIDEntryIDEntryIDEntry11
1

the result would be the name of those tables where IDEntry=@.ID where in this case @.ID = 1

Table1

Table2

Table4

|||I am sorry I still did not understand your table structure. So you have 3 tables all with ID = 1? this is not possible. ID is unique across all objects in a DB. Unless you have your own Id's for each table in your user tables?|||

I have seven tables, I want to know the name of each table where in thecolumn IDEntry I have the number 22(every table has this column)...this numberis unique and can be present in every table or only in some.

I know that I can do seven selects and see if this number exists in thespecified column. If it exists I can hard code the name of that table but thisisn't a nice way of doing. I thought that perhaps there is another way.

|||

If the tables are not related, you have to do separate SELECT's. If you use a stored proc you wouldnt have to worry about round trips. If you just want to check if the value exists you can do

IF EXISTS( SELECT * From Table1 WHERE IDEntry IS NOT NULL)

BEGIN

-- do something

END

I am still not sure if I understand your question. I am trying.

|||

He wants a query that looks inside a specific database, and returns the names of all the tables that have a column named "IDEntity" in which the value "22" exists in a row in that column. I'm just too lazy to write it for him.

Best answer I have would be to run a query similiar to what he has in his first post to get a list of table names (limited by tables that also have a column named IDEntity), then build a dynamic query to check each table and generate a string in the form:

SELECT @.tablename

FROM @.tablename

WHERE IDEntity=22

UNION

...

repeat for each value returned by the original query, replacing @.tablename with a hardcoded string prior to trying to execute it.

|||

DECLARE

@.varas varchar(8000)

DECLARE

@.colas varchar(20)

SET

@.col='IDEntity'

DECLARE

@.valas varchar(10)

SET

@.val='22'

SELECT

@.var=CASEWHEN @.varISNULLTHEN'SELECT '''+c.TABLE_CATALOG+'.'+c.TABLE_SCHEMA+'.'+c.TABLE_NAME+''' FROM '+c.TABLE_CATALOG+'.'+c.TABLE_SCHEMA+'.'+c.TABLE_NAME+' WHERE '+@.col+'='''+@.val+''''ELSE @.var+' UNION SELECT '''+c.TABLE_CATALOG+'.'+c.TABLE_SCHEMA+'.'+c.TABLE_NAME+''' FROM '+c.TABLE_CATALOG+'.'+c.TABLE_SCHEMA+'.'+c.TABLE_NAME+' WHERE '+@.col+'='''+@.val+''''END

FROM

INFORMATION_SCHEMA.COLUMNS c

JOIN

INFORMATION_SCHEMA.TABLES tON c.TABLE_CATALOG=t.TABLE_CATALOGAND c.TABLE_SCHEMA=t.TABLE_SCHEMAAND c.TABLE_NAME=t.TABLE_NAME

WHERE

COLUMN_NAME=@.colAND TABLE_TYPE='BASE TABLE'

EXEC

(@.var)

That should be good for about 20 or so tables. If you are running on SQL Server 2005 or above you can change the definiation of @.var to varchar(max) and then it should work for any number of tables.

|||

Hi Motley,

This is what I was looking for.

Thank you.