October 6, 2014 at 2:37 pm
Greetings folks,
I'm having a hard time wrapping my head around what is probably a very easy concept and I've had a hard time googling to track down the answer. Perhaps it's the verbiage I've used for my searches, but I haven't found what I'm looking for.
I've a database with thousands of tables - a lot of which need to be deleted as they've not historically been maintained. However, some of them need to remain.
The condition that I need to select the name from sys.tables is as follows:
Select name from sys.tables where
DB1.CustomerID.dbo is not in DB1.CustomerContact.dbo
It's obvious that I'll need to declare a tablename variable, so that it can run through all of the tables systematically.
This is how far I got before my brain broke and I'm just having a hell of a time thinking through what I need to do
USE [MyDB]
DECLARE @TABLE varchar(1000)
DECLARE @SQL varchar(4000)
SELECT @table = MIN(name) from sys.objects where type = 'U' and 'MyDB.dbo.' +name IN (select *brain died here*)
SET @sql = 'USE MyDB GO EXEC sp_rename dbo.' + @table + ', 'DELETE-'+ @table + '; GO
If I haven't provided enough info to allow you kind folks to assist let me know what I've exluded that's essential. Thank you very much for your time.
October 6, 2014 at 11:00 pm
... and I'm having a hard time understanding what you want.
Do you mean you're trying to find all the tables that do not contain a column with a given name? You can get all the table and column names in the database from the INFORMATION_SCHEMA table. You can write stored procedures against it just like you can against any other table.
October 7, 2014 at 3:26 am
pietlinden (10/6/2014)
... and I'm having a hard time understanding what you want.
+1
I also don't really understand what you are trying to accomplish.
Select name from sys.tables where
DB1.CustomerID.dbo is not in DB1.CustomerContact.dbo
--> why is dbo at the end?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 7, 2014 at 6:24 am
I'm not sure what you're trying to accomplish either. Basically, it sounds like you want to rename a bunch of tables, but haven't said exactly which ones. I presume they can be queried somehow, which means you could use a query to generate the statement you need by using something like this, but this will generate a row for each table. Please note that this is a shot in the dark.
select 'execute sp_rename ' + quotename(so.name) + ', ' + quotename('DELETE_' + so.name) + ';'
from sys.objects so
where so.type = 'u'
order by so.name;
You could add to the where clause to restrict the list, but I don't understand what your criteria is for inclusion. My guess is that this isn't what you're looking for, so could you please try to restate what you're after?
October 7, 2014 at 8:11 am
Apologies for the lack of clarity folks.
In the simplest of terms I am trying to retrieve a list of tables, but only tables that meet a condition.
DB1 has 13,000 tables named in the following format: XY#####
The schema of each of these tables is completely identical.
One of the columns in these tables is VARIABLE1.
There is another table called COMPARETABLE. This table also has a column named VARIABLE1.
What I'm trying to do is generate a list of tables where the primary key value of XY#####.VARIABLE1 does not have a match in COMPARETABLE.VARIABLE1
I sincerely hope that makes more sense than the OP.
October 7, 2014 at 8:19 am
Herpington_McDerpington (10/7/2014)
Apologies for the lack of clarity folks.In the simplest of terms I am trying to retrieve a list of tables, but only tables that meet a condition.
DB1 has 13,000 tables named in the following format: XY#####
The schema of each of these tables is completely identical.
One of the columns in these tables is VARIABLE1.
There is another table called COMPARETABLE. This table also has a column named VARIABLE1.
What I'm trying to do is generate a list of tables where the primary key value of XY#####.VARIABLE1 does not have a match in COMPARETABLE.VARIABLE1
I sincerely hope that makes more sense than the OP.
Is it clear? Yep, as clear as Malevich's Black Square...
Is the VARIABLE1 column in each of XY##### tables represents Primary Key?
I guess XY##### table may contain more than one value in the VARIABLE1 column, do you want to check if none of them found in COMPARETABLE.VARIABLE1, or if at least one value in XY#####.VARIABLE1 is not found COMPARETABLE.VARIABLE1 you still want to flag your XY##### table?
October 7, 2014 at 8:36 am
Eugene Elutin (10/7/2014)
Is it clear? Yep, as clear as Malevich's Black Square...Is the VARIABLE1 column in each of XY##### tables represents Primary Key?
I guess XY##### table may contain more than one value in the VARIABLE1 column, do you want to check if none of them found in COMPARETABLE.VARIABLE1, or if at least one value in XY#####.VARIABLE1 is not found COMPARETABLE.VARIABLE1 you still want to flag your XY##### table?
Hello Eugene, thank you for your time sir!
VARIABLE1 in the XY##### is a primary key. Each row has a unique identifier in this column.
COMPARETABLE.VARIABLE1 is either going to have a matching unique identifier in this column, or it's not. I want to generate a list of tables where there is not a matching unique identifier in COMPARETABLE. So I believe the query would include a clause to the tune of:
WHERE XY#####.VARIABLE1 NOT IN (SELECT VARIABLE1 from COMPARETABLE.VARIABLE1)
That's easy enough on an individual table basis. It's the T-SQL that's tripping me up with how I can declare the table names and have it present me with a list.
October 7, 2014 at 8:37 am
Herpington_McDerpington (10/7/2014)
Apologies for the lack of clarity folks.In the simplest of terms I am trying to retrieve a list of tables, but only tables that meet a condition.
DB1 has 13,000 tables named in the following format: XY#####
The schema of each of these tables is completely identical.
One of the columns in these tables is VARIABLE1.
There is another table called COMPARETABLE. This table also has a column named VARIABLE1.
What I'm trying to do is generate a list of tables where the primary key value of XY#####.VARIABLE1 does not have a match in COMPARETABLE.VARIABLE1
I sincerely hope that makes more sense than the OP.
Let me see if I understand your request here. You have 13,000 tables in your database with specific naming convention. You then need to look at every row of all 13,000 tables to see if any row in that table has a corresponding row in another table. So in other words you essentially need to run something like this for EACH of the 13,000 tables.
select 'XY1234'
from XY1234 t
join CompareTable ct on ct.Variable1 = t.Variable1
Are you prepared for how long this is going to take? You have no choice but to execute 13,000 queries here to get a list of table names.
_______________________________________________________________
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/
October 7, 2014 at 8:43 am
Sean Lange (10/7/2014)
Let me see if I understand your request here. You have 13,000 tables in your database with specific naming convention. You then need to look at every row of all 13,000 tables to see if any row in that table has a corresponding row in another table. So in other words you essentially need to run something like this for EACH of the 13,000 tables.
select 'XY1234'
from XY1234 t
join CompareTable ct on ct.Variable1 = t.Variable1
Are you prepared for how long this is going to take? You have no choice but to execute 13,000 queries here to get a list of table names.
Sean - yes that's what I'm trying to achieve, but I was hoping that it could be done by declaring the table name as a variable and having it automagically go through all tables. I've historically gone to excel and put together some concatenations and just execute in batches of scripts....but I'm trying to establish a better grasp of T-SQL and thought there must be an easier way.
I guess that if it's not doable that way then there's some sort of consolation with me being unable to figure it out myself haha.
October 7, 2014 at 8:48 am
Herpington_McDerpington (10/7/2014)
Sean Lange (10/7/2014)
Let me see if I understand your request here. You have 13,000 tables in your database with specific naming convention. You then need to look at every row of all 13,000 tables to see if any row in that table has a corresponding row in another table. So in other words you essentially need to run something like this for EACH of the 13,000 tables.
select 'XY1234'
from XY1234 t
join CompareTable ct on ct.Variable1 = t.Variable1
Are you prepared for how long this is going to take? You have no choice but to execute 13,000 queries here to get a list of table names.
Sean - yes that's what I'm trying to achieve, but I was hoping that it could be done by declaring the table name as a variable and having it automagically go through all tables. I've historically gone to excel and put together some concatenations and just execute in batches of scripts....but I'm trying to establish a better grasp of T-SQL and thought there must be an easier way.
I guess that if it's not doable that way then there's some sort of consolation with me being unable to figure it out myself haha.
You can use dynamic sql to help but at the end of the day you are going to end up with a crap load of queries hitting your db no matter how you do it.
_______________________________________________________________
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/
October 7, 2014 at 8:53 am
Yeah, there's no way around that for sure.
Thank you to everyone that replied and tried to understand what the hell I was trying to achieve. I greatly appreciate everyone's time and effort!
October 7, 2014 at 8:59 am
you can try:
DECLARE @sql NVARCHAR(MAX) = ''
CREATE TABLE #tResults (TableName VARCHAR(50))
SELECT @sql = @sql + 'INSERT #tResults SELECT ''' + st.name + ''' WHERE ' +
'NOT EXISTS (SELECT 1 FROM COMPARETABLE c JOIN ' + st.name + ' t ON c.VARIABLE1 = t.VARIABLE1);' + CHAR(13) + CHAR(10)
FROM sys.tables st
WHERE st.name LIKE 'XY%' AND EXISTS(SELECT 1 FROM sys.columns sc WHERE sc.[object_id] = st.[object_id] AND sc.name = 'VARIABLE1')
EXEC (@sql)
SELECT * FROM #tResults
Having 13000 tables may make @sql too large. then you can chang it to cursor and execute sql per table. In tbhis case it will make no performance difference...
Please note the SQL syntax used. It ensures that the table name will be fetched in case of XY#### table is empty
October 7, 2014 at 9:04 am
Eugene Elutin (10/7/2014)
you can try:
DECLARE @sql NVARCHAR(MAX) = ''CREATE TABLE #tResults (TableName VARCHAR(50))
SELECT @sql = @sql + 'INSERT #tResults SELECT ''' + st.name + ''' WHERE ' +
'NOT EXISTS (SELECT 1 FROM COMPARETABLE c JOIN ' + st.name + ' t ON c.VARIABLE1 = t.VARIABLE1);' + CHAR(13) + CHAR(10)
FROM sys.tables st
WHERE st.name LIKE 'XY%' AND EXISTS(SELECT 1 FROM sys.columns sc WHERE sc.[object_id] = st.[object_id] AND sc.name = 'VARIABLE1')
EXEC (@sql)
SELECT * FROM #tResults
Having 13000 tables may make @sql too large. then you can chang it to cursor and execute sql per table. In tbhis case it will make no performance difference...
Please note the SQL syntax used. It ensures that the table name will be fetched in case of XY#### table is empty
That's awesome Eugene, thank you so much! I'll give this a spin in dev and see how it goes!
October 7, 2014 at 11:58 am
Sorry missed page 2.
October 9, 2014 at 9:58 am
Eugene Elutin (10/7/2014)
you can try:
DECLARE @sql NVARCHAR(MAX) = ''CREATE TABLE #tResults (TableName VARCHAR(50))
SELECT @sql = @sql + 'INSERT #tResults SELECT ''' + st.name + ''' WHERE ' +
'NOT EXISTS (SELECT 1 FROM COMPARETABLE c JOIN ' + st.name + ' t ON c.VARIABLE1 = t.VARIABLE1);' + CHAR(13) + CHAR(10)
FROM sys.tables st
WHERE st.name LIKE 'XY%' AND EXISTS(SELECT 1 FROM sys.columns sc WHERE sc.[object_id] = st.[object_id] AND sc.name = 'VARIABLE1')
EXEC (@sql)
SELECT * FROM #tResults
Having 13000 tables may make @sql too large. then you can chang it to cursor and execute sql per table. In tbhis case it will make no performance difference...
Please note the SQL syntax used. It ensures that the table name will be fetched in case of XY#### table is empty
Just an update - Eugene that script has worked famously in Dev with the test tables I've put together and tested against. Thanks so much for taking the time to assist, I greatly appreciate it.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply