Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SQLIsTheDevil
Posting Yak Master
177 Posts |
 Posted - 2009-02-11 : 14:58:28
|
| Okay, I have a specific table (let's call it TableKing) with a column of data type datetime (let's call it DateCol). I have other tables with the same column, DateCol; however, these tables need to be updated, so as to reflect TableKing. Plus, I have many tables where this change needs to occur. Let's say DateCol in TableKing has a value of 12-12-1999. I have to make sure all the other tables that have a column called DateCol have the exact same value. So, I'm making a stored procedure that will retrieve the value of DateCol in TableKing and update the other tables accordingly. I thought about making a query in the proc where I retrieve the names of the tables that have DateCol. Then cycle through this list and update the tables in this fashion. This way, I keep the stored procedure short and I don't have to make changes to the stored procedure if I add another table with DateCol as a field. The problem is I don't know how to query the names of tables with a certain column and there's also the inconvienence of no array concept in T-SQL. I can probably cycle through using a loop or cursor, I suppose.Edit: I found a way using information_schema.columns, which is great...The problem I just realized is of the tables that have DateCol, only certain ones apply. But there still quite a few. If there's no way to get the names of the tables in this fashion, then it'd be great if there was a way I could some type of array-like functionality within the proc to accomplish this task.Again, any help would be appreciated.Thank you. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-02-12 : 10:48:54
|
Well, you can pass XML data as text. There is your array.
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
SQLIsTheDevil
Posting Yak Master
177 Posts |
Posted - 2009-02-12 : 11:53:23
|
| Fantastic. Thank you.I have an issue that is related to the same problem, however.I want to do something like this:DECLARE @NameOfTable varchar(50)DECLARE @Col2_Value intDECLARE @Switch int-- I'm cycling through tables, but i will set the @NameOfTable-- statically here just to keep things as simple as possible.SET @NameOfTable='MyLovelyTable'SET @Col2_Value = 0 -- The line below is pseudo code, as I realize it would never workIF EXISTS (Select @Switch=Col1 From @NameOfTable Where Col2=@Col2_Value)BEGIN-- Do some stuff-ENDHere's the problem. I need to set Col1 to @Switch, for each table I cycle through. I tried using DSQL, but the problem is DSQL has its own instance, thereby @Switch wouldn't be recognized. I could declare @Switch inside the DSQL statement, but then that doesn't really help me. The question is how do I store the value from this select statement into @Switch. I'm assuming DSQL is a dead-end, but I don't know. Must I create a UDF or second stored proc?Thank you. |
|
|
|
|
|
|
|
|
Retrieving names of tables based on columns