May 11, 2011 at 2:43 am
Hello
I just want to know how to query within a table by using its object_id
for example
Select * from table_name(id);
the result should be all column's conents of the table
Thanks
May 11, 2011 at 3:25 am
On the face of it this seems an odd thing to want to do.
No simple way I can think of apart I suppose from creating a string of SQl by concatenating select * with the table_name obtained by looking up the object_id, but I would normally try to avoid that sort of thing if possible.
What are you trying to achieve?, and in what language are you trying to do it? that may provide more options.
Mike
May 11, 2011 at 3:37 am
I have a table with field type nvarchar contains a name of another table
now I need to query the 1st table and within the query I want to query the 2nd one under a condition of the same refID in both tables
I'm using c#
Thanks
May 11, 2011 at 8:31 am
This sounds like a really strange requirement. Do you have a column in your first table that tells you which table the additional information is stored? Are you planning on retrieving this information one row at a time or are you hoping to get multiple rows at one shot? From the way it sounds on this end it sounds like there are some really serious architecture issues going on.
Please read and review the link in my signature about how to post questions so that you have the best chance at getting a solid solution to your problems.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 11, 2011 at 10:17 am
Forget the object_ID. You need to know the object name for a table_source in the SELECT ... FROM.
I can think of two approaches to this. One involves dynamic SQL, the other involves joining multiple tables and using a case statement for the values. How many different tables names are you going to store in table 1?
Please take the time to read the link Sean posted and give us the CREATE TABLE and some sample data for your table(s). If you do, you will find a number of people quite willing to offer you coded and tested solutions.
Finally, although you are coding in C#, the database queries are going to be written in SQL. The nature of your question suggests some fundamental conceptual gaps about SQL and relational database design. I strongly advise you to get the assistance of someone in your area who understands SQL, until such time as you can educate yourself.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
May 11, 2011 at 10:57 am
The Dixie Flatline (5/11/2011)
Forget the object_ID. You need to know the object name for a table_source in the SELECT ... FROM.I can think of two approaches to this. One involves dynamic SQL, the other involves joining multiple tables and using a case statement for the values. How many different tables names are you going to store in table 1?
Please take the time to read the link Sean posted and give us the CREATE TABLE and some sample data for your table(s). If you do, you will find a number of people quite willing to offer you coded and tested solutions.
Finally, although you are coding in C#, the database queries are going to be written in SQL. The nature of your question suggests some fundamental conceptual gaps about SQL and relational database design. I strongly advise you to get the assistance of someone in your area who understands SQL, until such time as you can educate yourself.
The dynamic SQL is a suitable approach for my case ,, and about coding language , I know that this issue is not related to c# ,, but to T-SQL ,, just in case it was an answer for a question asked before , number of table names stored in the main table are varying from 20 to 100s ,, its names are dynamically generated in runtime from another 3rd party table as a uniqueidentifier format string.
May 11, 2011 at 11:04 am
I suspect that dynamic sql is the only way you are going to have a chance. If you can post some ddl and sample data along with desired output we can get you going.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 11, 2011 at 11:30 am
Have a look on this,
use master
Select * from sys.sysobjects where xtype='u' order by id
Go
declare @Name varchar(100)
Select @Name=Object_Name(1131151075)
exec ('Select * from '+@Name)
-- Select * from spt_values
Go
Thanks
Parthi
May 11, 2011 at 12:51 pm
parthi-1705 (5/11/2011)
Have a look on this,use master
Select * from sys.sysobjects where xtype='u' order by id
Go
declare @Name varchar(100)
Select @Name=Object_Name(1131151075)
exec ('Select * from '+@Name)
-- Select * from spt_values
Go
I tried the following code before and it works fine except that I need to supply the @jtName variable dynamically inline the query.
DECLARE @jtName nvarchar(max);
DECLARE @qry nvarchar(max);
SET @jtName = '0c183530-9a0b-447b-8bf5-ac5b2ed36231';
SET @qry =
'SELECT Journal.RefName, ['+@jtName+'].Debit,
['+@jtName+'].Credit, Currency.CurrencyName, ['+@jtName+'].CurrencyExchangeRate,
['+@jtName+'].Note, ['+@jtName+'].AddingDate, ['+@jtName+'].AddedBy,
['+@jtName+'].RefTableName, Accounts.AccountName
FROM Journal INNER JOIN
['+@jtName+'] ON Journal.UID = ['+@jtName+'].UID INNER JOIN
Currency ON ['+@jtName+'].CurrencyID = Currency.ID INNER JOIN
Accounts ON Accounts.UName = Journal.JTUIDN';
EXEC(@qry);
May 11, 2011 at 1:01 pm
I tried the following code before and it works fine except that I need to supply the @jtName variable dynamically inline the query.
DECLARE @jtName nvarchar(max);
DECLARE @qry nvarchar(max);
SET @jtName = '0c183530-9a0b-447b-8bf5-ac5b2ed36231';
SET @qry =
'SELECT Journal.RefName, ['+@jtName+'].Debit,
['+@jtName+'].Credit, Currency.CurrencyName, ['+@jtName+'].CurrencyExchangeRate,
['+@jtName+'].Note, ['+@jtName+'].AddingDate, ['+@jtName+'].AddedBy,
['+@jtName+'].RefTableName, Accounts.AccountName
FROM Journal INNER JOIN
['+@jtName+'] ON Journal.UID = ['+@jtName+'].UID INNER JOIN
Currency ON ['+@jtName+'].CurrencyID = Currency.ID INNER JOIN
Accounts ON Accounts.UName = Journal.JTUIDN';
EXEC(@qry);
What do you mean you have to supply the name dynamically? That is what you are doing.
You could also greatly simplify this by aliasing your table like this
DECLARE @jtName nvarchar(max);
DECLARE @qry nvarchar(max);
SET @jtName = '0c183530-9a0b-447b-8bf5-ac5b2ed36231';
SET @qry =
'SELECT Journal.RefName, jt.Debit,
jt.Credit, Currency.CurrencyName, jt.CurrencyExchangeRate,
jt.Note, jt.AddingDate, jt.AddedBy,
jt.RefTableName, Accounts.AccountName
FROM Journal
INNER JOIN [' + @jtName + '] jt ON Journal.UID = jt.UID
INNER JOIN Currency ON jt.CurrencyID = Currency.ID
INNER JOIN Accounts ON Accounts.UName = Journal.JTUIDN';
EXEC(@qry);
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 11, 2011 at 1:08 pm
Sean Lange (5/11/2011)
I tried the following code before and it works fine except that I need to supply the @jtName variable dynamically inline the query.
DECLARE @jtName nvarchar(max);
DECLARE @qry nvarchar(max);
SET @jtName = '0c183530-9a0b-447b-8bf5-ac5b2ed36231';
SET @qry =
'SELECT Journal.RefName, ['+@jtName+'].Debit,
['+@jtName+'].Credit, Currency.CurrencyName, ['+@jtName+'].CurrencyExchangeRate,
['+@jtName+'].Note, ['+@jtName+'].AddingDate, ['+@jtName+'].AddedBy,
['+@jtName+'].RefTableName, Accounts.AccountName
FROM Journal INNER JOIN
['+@jtName+'] ON Journal.UID = ['+@jtName+'].UID INNER JOIN
Currency ON ['+@jtName+'].CurrencyID = Currency.ID INNER JOIN
Accounts ON Accounts.UName = Journal.JTUIDN';
EXEC(@qry);
What do you mean you have to supply the name dynamically? That is what you are doing.
You could also greatly simplify this by aliasing your table like this
DECLARE @jtName nvarchar(max);
DECLARE @qry nvarchar(max);
SET @jtName = '0c183530-9a0b-447b-8bf5-ac5b2ed36231';
SET @qry =
'SELECT Journal.RefName, jt.Debit,
jt.Credit, Currency.CurrencyName, jt.CurrencyExchangeRate,
jt.Note, jt.AddingDate, jt.AddedBy,
jt.RefTableName, Accounts.AccountName
FROM Journal
INNER JOIN [' + @jtName + '] jt ON Journal.UID = jt.UID
INNER JOIN Currency ON jt.CurrencyID = Currency.ID
INNER JOIN Accounts ON Accounts.UName = Journal.JTUIDN';
EXEC(@qry);
Well ,, simplifying is not a big issue for now 🙂
but what I meant that I supplied the table name manually in the code ,,, but indeed the "Journal" table contains hundreds of fields that contains different value for @jtName variable.
May 11, 2011 at 1:16 pm
So there is a field in each record of the Journal table that tells you which table name to join to?? This is what I have been suspecting all along. This is going to incredibly nasty and horribly slow. Is this a one time thing or something you need to do repeatedly? If it is a one time thing it is not going to be fast but not a huge deal. If this is something you need to do all the time...most likely you will not be happy with the performance.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 12, 2011 at 1:25 am
Sean Lange (5/11/2011)
So there is a field in each record of the Journal table that tells you which table name to join to?? This is what I have been suspecting all along. This is going to incredibly nasty and horribly slow. Is this a one time thing or something you need to do repeatedly? If it is a one time thing it is not going to be fast but not a huge deal. If this is something you need to do all the time...most likely you will not be happy with the performance.
Yes ,, I appreciate your efforts ,,, thank you.
May 12, 2011 at 7:06 am
If you want help figuring it out post some ddl and some sample data and we can give it a whirl.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 12, 2011 at 7:28 am
Sean Lange (5/12/2011)
If you want help figuring it out post some ddl and some sample data and we can give it a whirl.
Indeed I changed my plan and created a different schema without the idea of creating GUID named tables on the fly.
Thanks for all.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply