September 27, 2010 at 7:50 pm
Hi All,
Actually I have got a task to match all the tables of 2 servers (QA and PROD) and add tables to which
ever database doesn't have one or is missing... inshort both databases should have the same tables. Its a huge DB with thousand's of table.
How I am planning to move them is by making a SSIS but I want to know that IS there a way how I can copy all tables names to excel or flat file so that I can compare and match the table names in excel if they are present or not. I dont have access to fire a query on sys files
Any other way how I can retrieve all the table names from the database.
I would appreciate your help 🙂
Thanks
September 27, 2010 at 9:23 pm
To find table names through t-sql one needs permissions on some of the sys*.* tables. Here[/url] is an example.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
September 27, 2010 at 11:06 pm
I have tried to solve it in alternate way but through t-sql you will definitely need permission on sys tables.
Omeswar Danwada
September 28, 2010 at 9:56 am
Have you looked into any 3rd party applications? There are quite a few out there, but the one we have most recently started using is Apex SQL Diff
Chris Powell
George: You're kidding.
Elroy: Nope.
George: Then lie to me and say you're kidding.
September 28, 2010 at 10:24 am
I have been using the free version of SQL Effects Clarity for years, especially when the databases to compare are on two different servers. They have since changed the name of the software to SQL Accord CE.
http://www.sqleffects.com/Articles/Product/sqlAccordInfo/aboutSqlAccordCommunityEd.html
September 28, 2010 at 10:43 am
It's sounds like someone has given you a task but not the authority to efficiently complete it.
Try this in escalating order:
1) request temporary access to query the DMV's
2) give someone who does have permission a query and ask him to run it
3) tell your boss since you don't have the support you need you will have to count and compare by hand and not only will it eat precious man hours and be prone to errors, but you will complete your task... sometime... soon...
All joking aside try this:
exec sp_MSforeachtable ' PRINT ''?'' '
And by the way, you aren't thinking of just arbitrarily adding tables to PROD are you?
September 28, 2010 at 10:56 am
I would also ask for current backup copies of both databases. Then you can install them on your local machine with all the power and authority you need.
September 28, 2010 at 11:07 am
This code will show you which tables are missing from each environment. Note that it will require a linked server from one server to another (or if running from your workstation, a linked server to both).
Just change "PROD" to your prod server name, "QA" to your QA server name, and "db" to the appropriate database name on each server.
WITH CTE AS
(
SELECT SchemaName = s.name,
tablename = t.name
FROM PROD.db.sys.tables t
JOIN PROD.db.sys.schemas s
ON t.schema_id = s.schema_id
EXCEPT
SELECT SchemaName = s.name,
tablename = t.name
FROM QA.db.sys.tables t
JOIN QA.db.sys.schemas s
ON t.schema_id = s.schema_id
)
SELECT 'Missing from QA', *
FROM CTE;
WITH CTE AS
(
SELECT SchemaName = s.name,
tablename = t.name
FROM QA.db.sys.tables t
JOIN QA.db.sys.schemas s
ON t.schema_id = s.schema_id
EXCEPT
SELECT SchemaName = s.name,
tablename = t.name
FROM PROD.db.sys.tables t
JOIN PROD.db.sys.schemas s
ON t.schema_id = s.schema_id
)
SELECT 'Missing from PROD', *
FROM CTE;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 28, 2010 at 1:17 pm
Thank you guys .. I simply used
use databasename
select name from sys.tables
order by name
and got the table names... compared in excel and got the difference..
I found out that there are around 500 tables that needs to be created .
Do I have to manually create all these tables and write SSIS package for each of the tables
one by one or do I have any alternate to create and copy tables all at a go...??
I dont have mass upload access in SSIS so will use oledb command to achieve this...
Any help would be appreciated 🙂
September 28, 2010 at 2:13 pm
Are you just trying to script out the table names and schema or include the data also?
Copy the databases to a test instance and do not do this on Prod until you understand how this works.
If it is just script out the missing tables the easiest way is to right click on the database that holds all the tables, go to tasks and generate scripts. From there be sure to have "if not exists" set to true, go to the tables select and then select only the tables you need. Script it out to a file. VERY IMPORTANT... Change the name of the database to the new database.... Do this all on a test instance first to see how it will work.......How long it takes and any possible impact.
If you can get a copy of Redgate SQL compare it will compare both tables and more importantly also identify if any of the tables are different in each database. Since there are 500 tables missing I think that you will find out that the existing tables could have differences also.
September 28, 2010 at 2:25 pm
Thanks Lowry
To make u more aware of the problem below are the point :
1. I cannot copy the database to test as its huge ..probably 100 gb or so .. and test server doesnt have that space.
2. I am not from DBA team so I dont have the admin level rights.
3. Actually our prod server got less of space so we had to move some prod data to test data so I
need to be careful with that as well.
4. I have to copy the tables with the data as well. Is there a way i can create the scripts for all these tables at a go.
5. Is there a dynamic way or something which get this doing.
6. Doing this work manually would be a very long one so just trying to know the most efficient one.
Kindly suggest.
September 28, 2010 at 3:02 pm
well.... IF I had your tasks, I would look at Redgate's SQL Compare and SQL Data compare. They will easily save you days of work.
www.red-gate.com/products/SQL_Data_Compare/index.htm
www.red-gate.com/products/SQL_Compare/index.htm
Between those two programs, you could have everything scripted out for you in a matter of hours. (depending on how much data is in the tables you need to move.) If those tables have thousands of records, you should probably look into bulk import/export.
Something else you could look into is SSMSTools which has a right click option to script out the data. (if we're talking less than a couple of hundred rows.)
September 28, 2010 at 3:58 pm
+1 for the Redgate tools. They have saved me tons of hours.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply