• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 358
  • Last Modified:

Query

We had started a new project, but we are just modifying the existing one. so we have dumped a database to a new one. and my project manager had done some changes to stored procedures and some tables.

My actual requirement is that i just want to know what tables / sps he has deleted / added how can i get the changes that had done between two databases. Hope you understand.

ex: db1               db2
   tbl1                 tbl1
   tbl2                 tbl2
   tbl3                 deleted in db2
                        tbl4 added in db2

so the result should be tbl3 and tbl4 (no need of the info. about added / deleted i will check it)
Note: i want queries for both tables and stored procedures.


Hope i clear,
Thanks in advance
0
toocrazy007
Asked:
toocrazy007
  • 3
  • 3
1 Solution
 
toocrazy007Author Commented:
if possible can i get the changes in columns of a table also. i mean what are the columns altered and deleted etc... for each table.
0
 
MohanKNairCommented:
Create a table , T1, in db1 server with fields (object_name, object_type, column_name) and populate the table with data dictionary information. Transport the table (export/import) to db2. Create a similar table, T2,  in db2 server.


Now compare table T1 and T2

Tables deleted in db2
select object_name, object_type from T1 minus select object_name, object_type from T2;

Tables added in db2
select object_name, object_type from T2 minus select object_name, object_type from T1;

Columns dropped in db2
select object_name, object_type, column_name from T1 minus select object_name, object_type, column_name from T2;

Columns added in db2
select object_name, object_type, column_name from T2 minus select object_name, object_type, column_name from T1;

0
 
ExpertAdminCommented:
This should give you a list of objects which exist in one database but not the other:

SELECT name, CASE xtype
            WHEN 'S' THEN 'System Object'
            WHEN 'U' THEN 'Table'
            WHEN 'P' THEN 'Stored Procedure'
            WHEN 'X' THEN 'Extended Function'
            WHEN 'FN' THEN 'Function'
            WHEN 'V' THEN 'View'
            WHEN 'F' THEN 'Foreign Key'
            WHEN 'PK' THEN 'Primary Key'
            WHEN 'D' THEN 'Default Constraint'
            WHEN 'UQ' THEN 'Unique Index'
            ELSE xType
      END
FROM {Database1}.dbo.sysobjects

WHERE name NOT IN (SELECT NAME FROM {Database2}.dbo.Sysobjects)

In this example, replace {Database1} and {Database2} with database names on your SQL server that you wish to compare. The resulting list will have one record for each object which DOES exist in Database1 but does NOT exist in database 2.

Hope this does what you need.

M@
0
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
ExpertAdminCommented:
Just saw the other question...

To get a list of changed columns between tableA and tableB:

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME ='TABLE1'
      AND (COLUMN_NAME NOT IN
            (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
                  WHERE TABLE_NAME = 'TABLE2'))


This basic query will only list columns that were added to Table1, but you can add criteria once you get the results list to find changed column data types, etc.

M@
0
 
toocrazy007Author Commented:
First one is fine but for the second one the two tables in different databases where i can specify the databases for that query.
0
 
ExpertAdminCommented:
OK...

INFORMATION_SCHEMA contains all information for all databses. If you have two tables named the same in different databases, you can add the criteria of ""

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME ='TABLE1'
     AND TABLE_CATALOG = '{Database1}'
     AND (COLUMN_NAME NOT IN
          (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
               WHERE TABLE_NAME = 'TABLE2'
                  AND TABLE_CATALOG = '{Database2}'))
     ORDER BY ORDINAL_POSITION                            --Puts the differences in order that they are in the table

OR...to make it more generic:


DECLARE @DB1 VARCHAR(100),
              @DB2 VARCHAR(100),
              @Table1 VARCHAR(100),
              @Table2 VARCHAR(100)

SET @DB1 = 'MyDatabase'
SET @DB2 = 'MyOtherDatabase'
SET @Table1 = 'NewTable'
SET @Table2 = 'OldTable'

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @Table1
     AND TABLE_CATALOG = @DB1
     AND (COLUMN_NAME NOT IN
          (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
               WHERE TABLE_NAME = @Table2
                  AND TABLE_CATALOG =@DB2)
     ORDER BY ORDINAL_POSITION

This could easily be made into a stored procedure like this:


CREATE PROCEDURE FindDifferences (@DB1 VARCHAR(100),
              @DB2 VARCHAR(100),
              @Table1 VARCHAR(100),
              @Table2 VARCHAR(100)
)

AS

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @Table1
     AND TABLE_CATALOG = @DB1
     AND (COLUMN_NAME NOT IN
          (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
               WHERE TABLE_NAME = @Table2
                  AND TABLE_CATALOG =@DB2)
     ORDER BY ORDINAL_POSITION


M@
0
 
toocrazy007Author Commented:
Hi ExpertAdmin,
I didn't test the information for my second query. i got a text file from my pm what has been altered but the info is very useful. so i am giving the points to ExpertAdmin, i already used the first solution.

thanks for great response.
toocrazy007
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now