How Can One Find Out If Two Tables Can Be Joinned Dynamically
Jul 25, 2006
Hi All,
Given a table, is there a way to programmatically find out what other tables and views it can be joinned with? Essentially, given two tables, two views, or a table and a view, I want to be able to find out if they can be joinned. I was able to find related tables with sp_fkeys, but this does not work all the time, and does not work at all with views. I also played with the information_schema but got mixed up and confused quickly.
I tried all the INFORMATION_SCHEMA on SQL 2000 andI see that the system tables hold pretty much everything I aminterested in: Objects names (columns, functions, stored procedures, ...)stored procedure statements in syscomments table.My questions are:If you script your whole database everything you end up havingin the text sql scripts, are those also located in the system tables?That means i could simply read those system tables to get any informationI would normally look in the sql script files?Can i quickly generate a SQL statement of all the indexes on my database?I read many places that Microsoftsays not to modify anything in those tables and not query them since theirstructure might change in future SQL versions.Is it safe to use and rely the system tables?I basically want to do at least fetching of information i want from thesystem tables rather than the SQL script files.I also want to know if it's pretty safe for me to make changes in thesetables.Can i rename an object name for example an Index name, a Stored Procedurename?Can i add a new column in the syscolumns table for a user table?Thank you
Hi! I am using VS 2003 (v 1.1) I need to generate tables with the values from the Backend (SQLServer 2000) database in C#.Net. How can i create the tables, tablerows, cells, etc. from the codebehind page of C#. I am having a very little knowledge about dynamic generation. Give me the complete code with can example (if possible)
I'm currently developing an RDF application which need to handle lots of datatypes. But I want to use SQL-Servers capabilities for efficient querying and sorting. Therefore, I've wanted to create a Main Table which stores a Reference to the Table where the Data is stored. The Data itself should get stored in a Datatype-specific Table.
The Typed Table might get created by something like:
public void CreateTypedLiterals(Type type) { String sql = String.Format( "CREATE TABLE [Literals_{1}] (" + "ID int DISTINCT NOT NULL, Value {1})",
// BUG: does not work // WARNING: introduces a potential sql-injection problem type.ToString() );
...
As you can see on the statements this solution makes many troubles. So I've wanted to implement it in a more fine way using a DataTable:
[SqlProcedure] public void CreateTypedLiterals(Type type) { DataTable TypedLiterals = new DataTable( String.Format("Literals_{0}", type.ToString())); TypedLiterals.Columns.Add( "ID", typeof(int), "DISTINCT NOT NULL"); TypedLiterals.Columns.Add("Value", type);
...
But I have totally no Idea how to fetch this result into the existing Database. It might be cool to simply access the Database as it would be a .NET Dataset in the form:
Folks, Is there any way I can dynamically alter the structure of a table? In other words if my Transact SQL statement returns 5 rows I want to alter an existing table and put in 5 columns. Strange?! Something like alter table add column+@i The alter table statement is within a Cursor and ideally @i should increment each time and alter the table to put in a new column. If I went 3 times thru the loop, I should have column1 column2 column3
added to the new table.
Any pointers would be greatly appreciated. Thanks much in advance.
I want to create a nested SP which will dump out the results of All Temp Tables that are currently created in the session. The purpose of this is for createing a generic debugging SP which will write the contents of all temp tables to a debug table (when a certain flag is set).
I need to know how to:
- Get a list of all temp tables created - Get a list from each temp table of the columns created.
I have 10 tables, table1, table2, table3, table4.......table10. all these tables have different structure. From each of these tables I want extract data and dump into flat file csv.
So i have OLEDB source and FlatFile Destination.
If i write seperate data flow task for each of the tables, then there is no issue.
But i want to use a single data flow task for all these tables. So for this, i use a variable @SQLStr . And i dynamically set the value of this variable to select * from table1, select * from table2.........selct * from table10.
So in the OLEDB source I select Data Access Mode as : SQL Command from variable. And I use @SQLStr for this varible.
For Destination, i dynamically generate the flat file for each of the table.
But this doesnt work, i get validation errors.
So, first can i use a single data flow task to dynamically change the source(different source tables) and destination. If so, what i am missing in the above process?
I want to write a SSIS which picks up the source and destination tables at runtime is it possible. As we have a SSIS which is used to pull data from oracle but the source and destination table name changes.
Hi friendsI have little problem here.I am creating data tables dynamically.I want to filter it using sql query.Suppose, I have four data tables with the same structure but records may be different.There are two fields ServiceMethod and Rates.Now I want to filter all tables and want to get match records with the ServiceMethod.Suppose,four records in First table,three records in other three tables,and only two records(Service method) are same in all tables.I want to that two records by filtering all tables and sum of rates and want to add matched records in new table and bind dropdownlist.Can any guide me how to filter more than one tables using sql query if data tables are created dynamically?Thanks in advance.
Sorry if this is a super-basic question...I'm used to join selects but not sure how to approach an append select (or something like it) I have two tables with identical field structures: a Master table with 10,000 rows and a Custom table table with 1,000 rows To keep it simple, let's say the two tables each have a FirstName field and a LastName Field. Is it possible to use a View or a Select statement (or any other method) to 'append' the rows of both tables so that the result set still has only the two columns (FirstName and LastName) and has 11,000 rows? Thanks for your help! Randy
Every day an application creates new tables and dumps static info into them.
I would like to create a package to dynamically export those database tables to raw files for long term archive, one file per table. Here is what I have so far and the issue I am having.
1) Get a list of un-archived tables. 2) Foreach table do the following.
a. Export the table into raw file. b. Zip the raw file. c. Update archive tracking table.
As long as the metadata for each table is the same this package seems to work fine. However, I have many tables with different metadata. How can I dynamically get the package update the metadata column collection when it hits a new table? When it hits a table with different metadata I am getting warnings like this:
The column "some_column" needs to be added to the external metadata column collection.
The "external metadata column "someother_column" (103)" needs to be removed from the external metadata column collection.
Then I get this error: Error: 0xC004706B at dump the table into a raw file, DTS.Pipeline: "component "OLE DB Source" (1)" failed validation and returned validation status "VS_NEEDSNEWMETADATA"
I have many tables that have as one of its keys POLICY_NUMBER is there a way to write a script that will look in all tables in a db for a certain POLICY_NUMBER
I modified my origianl script (used a cursor) to use a looping method I saw in a different thread http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=30286 by Nigel Rivett. Since I used code on this board I though I would post back for the greater SQL Team script toolbox. Feel free to use it to catch those deliquent sql programmers who forget to create a PK. Enjoy.
select @database = '', @maxdatabase = max(name) from master.dbo.sysdatabases where dbid > 5 while @database < @maxdatabase begin select @database = min(name) from master.dbo.sysdatabases where dbid > 5 and name > @database
set @cmd = 'select ''' + @database + ''', o.name from ' + @database + '.dbo.sysobjects o join ' + @database + '.dbo.sysindexes i on o.id=i.id where o.xtype=''U'' and i.indid = 0'
I am brand new to using SQL. I created a DB with a bunch of tables there. What I would like to do is from my VB.NET application I would like to allow the user to see what tables are available in that DB.
Also I need a way to check if a table exists. I used the following:
strTemp = "IF EXISTS " & _ "(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'simplesql') AND type in (N'U'))" cmd.Connection = conncmd.CommandType = CommandType.Textcmdcmd.ExecuteNonQuery()
but I get an exception : Incorrect syntax near ')'
Is there any way i can find some certain fields in the whole database? like i know a column called Type_ID in table Sys_Type, how can i know there are same Type_ID column in other tables? Thanks in advance.
Dear friends, is there any way to find the table names from a view?
ex: suppose i have a view named as vorganization.i need all the tables in this view. is it possible to get through a query? same case for procedures and functions also.........
I have a database with 150 tables. I need to find connections between specified tables. (I need to find through what fileds and tables are table1 and table2 connected) Is there any tool in SQL Server for this?
We have inventory table which is updated everyday. I need to find the gap between updated table vs history (which means yesterday's data). what i did is I just copy the inventory everyday before it is updated. So, whenver inventory table is updated I want to know the gap between these two (history vs updated) tables.
The structure of two tables are same and here are columns: date_key (fk) store_key(fk) item_key(fk) begin_inventory_dollars ending_inventory_dollars begin_inventory_units ending_inventory_units
I want to compare each row and if the value is changed I want to have a gap (updated value - history value) and if data is new then just want to add.
Here is tsql: however when I compare to gap between table from excel spreadsheet and this query, this query does not return a right value. (Some are correct but some don't)
Code Snippet select a.date_date_key, sum(a.Beg_Inventory_Dollars- (case when b.beg_inventory_dollars is NULL then 0 else b.Beg_Inventory_Dollars end)),
sum(a.Beg_Inventory_Units-(case when b.beg_Inventory_Units is NULL then 0 else b.beg_Inventory_Units end)) ,
sum(a.Ending_Inventory_Dollars- (case when b.Ending_Inventory_Dollars is NULL then 0 else b.Ending_Inventory_Dollars end)),
Sum(a.Ending_Inventory_Units-(case when b.Ending_Inventory_units is NULL then 0 else b.Ending_Inventory_Units end))
from UPDATED TABLE a
left outer join HISTORY TABLE b
on b.item_key = a.item_key and
b.store_key =a.store_key and
b.date_key =a.date_key
group by a.fisc_date_key
Did I miss something in here? Any kind of advice would be grealy appreciated. Thanks.
I need a query to find max serial number by comparing two different tables. Here is my requirementI am having two tables named Table1 and Table2. Each tables having more than 30,000,000 records.I want a simple query to find Max srno from two tables.For exampleIf Table1 max is 245 where partno=2 and ano=2and Table2 max is 343 where partno=2 and ano=2Then 343 is max serial noIf Table1 max is 435 where partno=2 and ano=2and Table2 max is 34 where partno=2 and ano=2Then 435 is max serial noI used this query but its taking more time select max(v.MaxSrNo) from ((select max(MaxSrNo) as MaxSrNo from Table1 where partno=@partno and ano=@ano)union all (select max(MaxSrNo) from Table2 where partno=@partno and ano=@ano)) as v Pls give me a simple query to find max srno.
How to find out all the statistics from all the tables and drop them..any script anyone can help with? When we are trying to make datatype changes in few related tables,it's giving error saying that some statistics are dependent onthe column blah blah...
My db contains two table Employee_detail and Student_Detail.Employee_detail has Emp_id and Student_Detail has stud_id.Now my problem is that i want to get one maximum no from these two table.
how to find the names of the tables owned by the particular user in sql server and how to display the distinct object types owned by the particular user.
i would like to know where can i find information on the concept of SQL like Base Table , View Table and Search Table as now i am using VS.Net 2005 and SQL Server 2005 to write a addressbook program and i am just a newbie to Sql and c#... i wish to write my sql commands in c# side before passing it into sql and may i know how to do it?
I am trying to query for a common value in a column called "file_auth_nbr" in 30 different tables. I was going to try something like this (see below) but wasn't sure if this was the most efficient, fastest, or correct, way to get what I'm looking for:
Select distinct a.file_auth_nbr from table1 as a join table2 as b on a.file_auth_nbr = b.file_auth_nbr join table3 as c on a.file_auth_nbr = c.file_auth_nbr join table4 as d on a.file_auth_nbr = d.file_auth_nbr join table5 as e on a.file_auth_nbr = e.file_auth_nbr ......etc., etc.
I'm doing some performance reviews and wish to know what tables SQL haspinned in memory and which ones have are loaded through usage ...Is there a way ?Thanks,Craig
I have two tables in the same SQL database. Both have a similar numericfield. One field has the same number as the other field but is prefixedwith various letters. I know how to use LIKE but won't know the partialstring I am looking for. I am trying to use LIKE '%' + Field A orsomething that will do this.Eg.Table 1 Field A is 'A12345"Table 2 Field B is '12345"I want to find every record in Table 1 Field A that contains the exactField B data.*** Sent via Developersdex http://www.developersdex.com ***
