Selecting Individual values from a table

Question

Post reply

Selecting Individual values from a table

vani_r14

SSCrazy

Points: 2843
More actions
January 20, 2010 at 8:35 pm

#386154

[font="Verdana"]Hi all
I am currently trying to figure out how to do this but am kind of stuck in between. first i will explain what I am trying to do and then how i have tried to do it
I would like to do the following -
1. Enter an episodenumber in as a parameter.
2. Goes through the database and searches all tables to see which table has the column episodenumber in it
3. Pulls out the list of tables in the temp table
4. Go through each of these tables and find out if they have the episode number that was entered as parameter
5. populate with an Y or N accordingly in the temp table
I have tried the following -
1. Realise how to create parameters and enter values
2. Checked out and pulled up a list of all tables which has the episode number column from the sysobjects table
When it comes to checking up to see which table has the specific episode number entered as the parameter I am stuck. I tried the following
1. I numbered each tabled uniquely and tried to join it but not working
2. I created a loop and printed out the table names and tried it not working either.
can someone please help
thanks
Vani [/font]

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

shanu.hoosen SSCrazy Points: 2227 More actions · Answer 1

hi there,

please post table structures and sample data

cheers

Nabha SSCrazy Eights Points: 8534 More actions · Answer 2

Just a guess work as you've not given us anything to work with. I think you need a dynamic sql to achieve whatever you are doing?

Something along these lines??

CREATE TABLE #TNAMES(TName varchar(50))

INSERT INTO #TNAMES VALUES ('a')

Declare @tableName nvarchar(100)

Declare @sql nvarchar(100)

DECLARE @SQLMAIN nVarchar(100)

DECLARE @WhereClause nvarchar(100)

SET @WhereClause = ' <Where Clause>'

SELECT @tableName = TNAME FROM #TNAMES

PRINT @tableName

SET @SQLMAIN = 'Select * from '

SET @sql = @SQLMAIN + @tableName + @WhereClause

EXEC sp_EXECutesql @sql

---------------------------------------------------------------------------------

LythamsFinest SSCommitted Points: 1786 More actions · Answer 3

Hi,

I spent a couple of minutes putting together a dynamic SQL solution to your problem. Hopefully I have understood what you are looking for.

What you need to do is set the value of @ColumnName to the name of your 'episode' column and @Value to the 'episode number'.

You can see that I have used VARCHAR(50) as the datatype and in the dynamic SQL I CAST the table column to the same datatype. You can change these if the datatype does not work for you.

The PRINT statement is there so you can see what SQL is being generated.

Enjoy! (hopefully)

SET NOCOUNT ON

DECLARE @ColumnNameSYSNAME

DECLARE @ValueVARCHAR(50)

DECLARE @sqlNVARCHAR(1000)

SET @ColumnName = 'xxxxxxxxxxxx'

SET @Value = 'nnnnnnnnn'

SET @sql = ''

SELECT @sql = @sql + CASE WHEN @sql = '' THEN '' ELSE 'UNION' + CHAR(10) END +

'SELECT TOP 1 ''' + obj.[Name] + ''' AS [TableWithColumnValue] FROM ' + QUOTENAME(sch.[Name]) +

'.' + QUOTENAME(obj.[Name]) +

' WHERE CAST(' + QUOTENAME(col.name) + ' AS VARCHAR(50)) = ''' +

@Value + '''' + CHAR(10)

FROM [sys].[objects] obj

INNER JOIN [sys].[columns] col ON col.[object_id] = obj.[object_id]

INNER JOIN [sys].[schemas] sch ON sch.[schema_id] = obj.[schema_id]

WHERE obj.[type] = 'U'

AND col.[name] = @ColumnName

PRINT @sql

EXEC sp_executeSQL @sql

vani_r14 SSCrazy Points: 2843 More actions · Answer 4

Hi all

Thanks for your help. I am trying to understand and implement it. But this is what I tried and it seems to be ok for now but am enhancing it soon with the suggestions that I have received. Thanks again for your help

declare @tname varchar(15)

declare @counter int set @counter = 1

declare @cntr int

while (@counter < (select min(tno)+1 from temp2))

begin

declare @result varchar(8000)

set @tname = (select name as tn55 from temp2 where tno = @counter)

set @result = ('SELECT * FROM ' + @tname)

exec (@result)

set @counter = @counter + 1

end

--end

update temp2 set tno = (select max(tno)+100 from temp2) where tno = @counter-1

Thanks

Vani

vani_r14 SSCrazy Points: 2843 More actions · Answer 5

Hi all

i have worked this out now. thanks for your solutions. i have used the tips given and its working now...

thanks again

Vani