Compare Schema's...XSD
Jul 20, 2005
Hey all,
I am currently researching ways to compare databases via an XSD schema.
I wrote a small app that creates a dataset from a database and exports
that dataset to XSD. This gives me an XSD file with tables and
relationships representing the entire database.
At this point, I am trying to find ways to compare these schemas. Does
anybody know of a way to do this easily and to record differences if
there are any?
Also, any information on comparing databases using any method would be
greatly appreciated.
Thanks,
--
Shock
View 3 Replies
ADVERTISEMENT
Dec 3, 1999
I've searched quite a bit, and have found several leads on schema, stored procedure, and database contents comparison scripts and tools.
I'm now looking for recommendations on which ones are best, easiest:
ObjCompare.exe
sb_ABCompareDb.sql
sp_db_comp.sql
There's a mythical script from Andrew Z <mumble> that Mike Hotek talks about...
There's a DBCompare on the Back Office Resource Kit 2 CD, which of course is not in the umpteen MSDN CDs :-(
There's some *other* command line dbcompare, or maybe db_compare.
There's a DBA Compare.
I need to be able to compare divergent schemas from two developers to integrate their changes, so need schema and stored procedures compared only, and would also like to have something to compare staging servers and production servers.
Leads on other choices also welcome. I'd be happy to summarize and post, if warranted.
View 1 Replies
View Related
Oct 6, 2006
In the process of purging data to history tables,
we wanted to make sure that no schema changes have been done
to the main or the history table.
So to ensure identical schemas, we use this function:
ALTER FUNCTION dbo.fnCompareTableSchema
(
@t1Name NVARCHAR(257)
,@t2Name NVARCHAR(257)
)
RETURNS BIT
AS
/*
Compares the schema of 2 tables
If the schema is different RETURNS 0
If the schema is identical between the two table, RETURNS 1
NOTE: system tables or non-existant tables that are NOT in INFORMATION_SCHEMA views will compare equal (RETURNS 1)
==================================================================================================================
SAMPLE USAGE:
DECLARE @schemaOK BIT
SELECT @schemaOK = dbo.fnCompareTableSchema('dbo.table1','dbo.table2')
IF @schemaOK = 1
PRINT 'TABLE SCHEMA IDENTICAL'
ELSE
PRINT 'TABLE SCHEMA DIFFERENT'
==================================================================================================================
*/
BEGIN
IF @t1Name = @t2Name
RETURN 1
-- check if schema is different
IF EXISTS
(
SELECT*
FROM
(
SELECTCOLUMN_NAME, ORDINAL_POSITION, DATA_TYPE
, COLUMN_DEFAULT, IS_NULLABLE
, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE
, COLLATION_NAME
FROMINFORMATION_SCHEMA.COLUMNS
WHERETABLE_SCHEMA = COALESCE(PARSENAME(@t1Name,2),'dbo') AND TABLE_NAME = PARSENAME(@t1Name,1)
UNION ALL
SELECTCOLUMN_NAME, ORDINAL_POSITION, DATA_TYPE
, COLUMN_DEFAULT, IS_NULLABLE
, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE
, COLLATION_NAME
FROMINFORMATION_SCHEMA.COLUMNS
WHERETABLE_SCHEMA = COALESCE(PARSENAME(@t2Name,2),'dbo') AND TABLE_NAME = PARSENAME(@t2Name,1)
) U
GROUP BY
COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE
HAVING COUNT(*) <> 2
)
RETURN 0
-- schema identical
RETURN 1
END
View 6 Replies
View Related
Apr 16, 2015
I am looking for some SQL Scripts/tool to compare the two sql database and generate the difference results into Excel file. I am not looking for Sync/change scripts.
Example:
Result Type Desc
-----------------------
Col1 Column Added
Table1 Table Removed
View 9 Replies
View Related
Jun 30, 2015
Following an upgrade to SQL Server 2012, our shop's Schema Compare tool (Redgate SQL Compare) is no longer supporting our environment.We are starting to evaluate various 3rd party products to find a possible replacement, and would be interested in what products are favored by other IT shops who do a lot of database work.
Our shop is split about 75% SQL Server, 20% Oracle, and 5% I'll call other. Ideally a product would support SQL Server and Oracle, but our focus is on SQL server right now. On that platform we have ~50 servers spread across DevUATProd environments.In basic terms, we need a tool that can identify schema differences between DBs and generate synchronization scripts to support deploys between environments. Real-time synchronization is not a requirement (nor desirable), as deploys are a gated DBA function in our shop.
View 2 Replies
View Related
Mar 12, 2008
Just wondering if there are any tools available for SQL Server 2005 which allow the comparison and scripting of data and schema between two databases. This is so that I can migrate between Dev, QA, and Live easily.
A free tool would be best please..
Thanks in advance.
Gaj
View 11 Replies
View Related
Aug 25, 2014
We are getting regular SSDT/VS crashes when doing schema compares (source: project, target: server). Sometimes the compare succeeds but around 80% of the time we get a crash.
We are using VS2013 Update 3 and the latest version of SSDT.
Application : devenv.exe
Version du Framework : v4.0.30319
Description : le processus a été arrêté en raison d'une exception non gérée.
Informations sur l'exception : System.Runtime.InteropServices.COMException
Pile :
  à System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32, IntPtr)
[code]....
View 5 Replies
View Related
Jan 3, 2013
I've downloaded and installed the latest SQL Server Data Tools for VS 2012. Â Is there anyway to export the results of the schema comparison into a report in CSV/Html format? Â I understand that it can generate the sql diff script, but I want a readable report that I can use to show to people.
View 5 Replies
View Related
Jul 8, 2015
I just recently updated to SSDT 12.0.50512.0 using Visual Studio 2013 Ultimate. I typically use SSDT Schema Compare to synchronize my schema across multiple databases and different environments. After updating i encountered a major bug while updating our production schema.Typically during schema compare, the compare will prompt me to drop users and user roles from the database as they are not present in the project. I will exclude these so they database users and their roles aren't affected. After the update to SSDT I noticed that schema compare was only prompting me to drop the User, but didn't show anything about the user's roles. Not thinking much of it I went through my usual task of updating all the production databases. I soon found out that this did in fact remove the user roles even though it showed NOTHING in the schema compare UI indicating it would do so.
GO
PRINT N'Dropping <unnamed>...';
GO
EXECUTE sp_droprolemember @rolename = N'db_datareader', @membername = N'dbuser';
GO
PRINT N'Dropping <unnamed>...';
GO
EXECUTE sp_droprolemember @rolename = N'db_datawriter', @membername = N'dbuser';
You could say this is partially my fault for not checking the generated script before running it, but after months of this routine task I've never had an issue until this update.i'm not seeing the changes that will happen to my user roles in the schema compare UI?Â
View 2 Replies
View Related
Jun 12, 2015
I'm trying to automate comparing the dacpacs we're generating from out builds against our production server to monitor drift.However, we use scripting variables to define cross database references.  The schema compare is showing up all the objects which reference the other database via scripting variable as being different to what is on the server i.e. it reports a change between a table referenced as  [$(db)].dbo.Table in the dacpac  and db.dbo.Table in the target database.
When I do a comparison in Visual studio between the project and the target database the variables seem to be appropriately replaced and the differences don't show. Â Obviously this is using a project instead of a dacpac but I'm hoping I can get the dacpac/db compare to behave similarly to the project/db comparison.
Is there a way to define what the scripting variables should resolve to when I run the comparison via msbuild?
Edit: Â I would prefer not to deploy the dacpac and diff the deployed db against the target database but if that's the only way....
View 5 Replies
View Related
Nov 22, 2012
I've made a comparison between a database project (sqlproj) and a database.
I can't exclude some items because they are dependent items on them. So they are implicitly included in the update script.
Unfortunately when I have unchecked all the items, some of them are still implicitly included.
It seems that there are circular dependency or some thing like that.
View 5 Replies
View Related
Jul 28, 2015
I am importing an existing database into a Visual Studio SQL Server Database project using Schema Compare. Â The Schema Compare works fine and I updated my project successfully. Â However, the project won't build because of the existence of 3-part names in some objects:
E.g. I import the database MyDB into a new project MyDB using Schema Compare. Â The database contains views with queries like this:
SELECT col1, col2. col3
FROM MyDB.dbo.MyTable
When trying to build this project I get errors like:
Error 39
SQL71561: View: [dbo].[vw_MyView] has an unresolved reference to object [MyDB].[dbo].[MyTable].
C:UsersRedirectionrittg2DocumentsVisual Studio 2012ProjectsMySolutionMyDBdboViewsvw_MyView_1.sql
but of course this is a bogus message since the view can clearly read from an object in the same database whether using 2-, 3-part naming (or 4-part naming for that matter).
How can I resolve these errors without editing the objects before running Schema Compare? (there are hundreds of them).
View 5 Replies
View Related
Aug 11, 2015
I have created Database projects in VS 2013 using SSDT. I have been mostly successful in creating and building the projects without any errors/warnings.
However for one of the databases in the project, when i do schema compare to apply the changes from a SQL Server Database to a Database Project in VS, code changes are not applied to the database project.
After i select the Update option in Schema compare window, I'm getting the following message
"Target update complete. Press Compere to refresh the comparison."
Even tough the message implies that target database is updated successfully, I do not see the objects i selected in schema compare being added to the target database project.
I see the following warning
"Target update: Could not update script for element 'dbo'"
I have 9 Database projects in the Solution and I'm able to apply changes to 8 of the database projects through schema compare successfully. I get the same warning after schema compare for all database projects.
I have same project level setting for all database projects in the solution. I'm using Visual Studio 2013 Premium Update 5 SQL Server Data Tools 12.0.41012.0
View 3 Replies
View Related
Jul 11, 2014
I have a database project where objects have been pulled in from the database using schema compare.
Unfortunately CDC tables which are referenced in stored procedures on the database have not been pulled in by the schema compare & hence I cannot build the project and deploy changes back to the database.
How to get these tables included in the project .
View 1 Replies
View Related
Sep 27, 2007
Locally I develop in SQL server 2005 enterprise. Recently I recreated my db on the server of my hosting company (in sql server 2005 express).I basically recreated the tables and copied the data in it.I now receive the following error when I hit the DB:The 'System.Web.Security.SqlMembershipProvider' requires a
database schema compatible with schema version '1'. However, the
current database schema is not compatible with this version. You may
need to either install a compatible schema with aspnet_regsql.exe
(available in the framework installation directory), or upgrade the
provider to a newer version.I heard something about running aspnet_regsql.exe, but I dont have that access to the DB. Also I dont know if this command does anything more than creating the membership tables and filling it with some default data...Any other solutions/thought on what this can be?Thanks!
View 4 Replies
View Related
May 27, 2008
I have 35+ tables and 15+ stored procedures with SchemaA, now I want to transfer them to SchemaB.
I know how to do one by one...!
alter schema SchemaB transfer
SchemaA.TableA
but it will take long time...!
Thanks,
View 3 Replies
View Related
Apr 12, 2008
Hello everybody!I'm using ASP.NET 3.5, MSSQL 2005I bought virtual web hosting .On new user registrations i have an error =(The 'System.Web.Security.SqlMembershipProvider' requires a database schema compatible with schema version '1'. However, the current database schema is not compatible with this version. You may need to either install a compatible schema with aspnet_regsql.exe (available in the framework installation directory), or upgrade the provider to a newer version. On my virtual machine it work fine but on web hosting i have an error =(What can you propose to me?
View 2 Replies
View Related
May 8, 2007
Hello,
I would like to use SSIS tool to move the data from one database schema to another database schema.
For example:
Source table has
1. UserName (varchar 20) (no null)
2. Email (varchar 50) (can be null)
Destination table has
1. UserID (uniqueidentifier - GUID)
2. UserName (varchar 50) (no null)
3. EmailAddress (nvarchar 50) (can be null)
4. DateTime
Questions:
1. What controls do I use in my Data Flow to make data move between databases with different data types and include new value in UserID as a new GUID and DateTime as a date (GETDATE)?
OLE DB Source, OLE DB Destination, Data Converson and .....
How do I insert Guid and Date at the same time?
2. I have many tables to do data moving. Any sugestions? How do I architect my project? If I create many data flows for each table - it will look complicated.
Please give me some advices here.
Thanks.
View 3 Replies
View Related
Apr 19, 2006
I used SSEUtil to add a schema to my database but I am having problems. Used these steps:SSEUtil -c> USE "c:Rich.mdf"> GO>!RUN Resume.SQL//indicates success>SELECT * FROM SYS.XML_SCHEMA_COLLECTIONS>GO//schema not shown in list> USE master>GO>SELECT * FROM SYS.XML_SCHEMA_COLLECTIONS>GO//schema is shown in the queryIt appears that the schema is not added to the desired database, so when I try to use the schema in Visual Studio, the schema does not appear when I connect to the Rich.mdf database. Any ideas on what I am doing wrong or why this might be happening?ThanksKevin
View 3 Replies
View Related
Nov 21, 2011
I am using sql server 2008 R2.I want to copy all the objects of one schema and put it in another schema. I want to do that from command prompt.
In oracle we can export the objects of one user and import to another user using exp and imp. I want similar type.
View 5 Replies
View Related
Jul 22, 2007
Table MediaImportLog
column ↘ImportIndex ImportFileTime ImportSource
value ↘80507 20060506001100 815
80511 20061109120011 CRD � P.S the values type of ImportFileTime 20060506001100 → 2006 -year 05-month 06-day 00-HH 11-minute 00-second】
Table BillerChain
column↘BillerInfoCode ChainCode
value ↘750 815
value ↘81162 CRD
Table Biller
column↘CompanyCode BillerCode
value ↘999 750
value ↘81162 516
TAble DataBackup
column↘CompanyCode Keepmonth
value ↘999 6
value ↘81162 12
---------------------------------------------------
when I'm in MediaImportLog , I want use column ImportSource to compare with column ChainCode in table BillerChain ( so I get BillerInfoCode) and then use the BillerInfoCode I got to compare with column BillerCode in Table Bill ( I get CompanyCode) finally I use CompanyCode to compare with column CompanyCode in table DataBackup so I can get the company's keepmonth
How can I get the keepmonth? can I use parameters ?
thank you very much
View 3 Replies
View Related
Mar 13, 2000
I remember reading that you can compare the schema of 2 databases to see if there are any diferences. For the life of me, I can not remember where I read it or what it is called. Any help would be great!
Thanks
Tammy Maxfield
View 2 Replies
View Related
Sep 30, 2004
How can I compare data in 1 column??
Thanks
View 4 Replies
View Related
Jan 25, 2006
Can you please help me figure out the best SQL Compare (structure and data) tool in the market.
Thanks,
Saurav
View 11 Replies
View Related
Mar 22, 2007
Hey all,
If we use operator < <= > >= to compare NON nummeric, then how it is being compared..? what it returns?
eg : .... WHERE someObj1.UniqueX <= someObj2.UniqueX
considering :-
someObj1.UniqueX someObj2.UniqueX
MM1 NN1
MM2 NN2
MM3 NN3
~~~Focus on problem, not solution~~~
View 7 Replies
View Related
Oct 27, 2007
Hi all !
i'm new in sql and new in database.
And my question :
i m trying to compare sql database tables,views,columns,stored procedures.
First i compared tables,columns, but i can't do it with stored Procedure.
Why ? Because stored Procedures are code...
Ex... When i Compare table and columns use these codes.
"if (sourceColumn.IsString)
{
strSize = sourceColumn.Type.ToString()+"(" + sourceColumn.Size + ")";
sqlAddColumn = string.Format(@"ALTER TABLE {0} ADD {1} {2}", target.Name, sourceColumn.Name.ToString(), strSize);
....
This is my StoredProcedure Compare method
public string Compare(StoredProcedure storedTarget)
{
string storedScript = string.Empty;
if (storedTarget.RoutineDefinition != this.RoutineDefinition)
{
// string storedScript = (@"ALTER PROCEDURE ); <--- How can i put new stored procedure ?????? What is sql code for this work ?
}
}
Thank You all sql guru s
View 5 Replies
View Related
Nov 26, 2007
I have an application that writes records with a timestamp to an sql table.
I wish to compare the values in each field and see if they have changed from the previous record and then delete if they haven't. Can anyone offer a script or procedure to do this please?
View 2 Replies
View Related
Jul 23, 2005
hi i doing a project like "IMesh", whatever ,i will take a sentencefrom the user and want to retrieve all row from the table has similarwordsfor example the user enter "programming with c#" so i retrieve allfields that contain "programming" or "c#"some thing like what a search engine dothanx
View 1 Replies
View Related
Apr 25, 2008
Hello All
i have a table with Three Fields
id StartSeqNo EndSeqNo
1 A000101 A000200
2 AA000101 AA000200
3 ABC1012A ABC2012B
Now if i pass 'ABC1015A'
The Query Should Check between StartSeqNo and EndSeqNo and should return that row
Please Help Me
Thanks in Advance
Regards
Balagangadharan
View 4 Replies
View Related
Dec 14, 2006
I need to compare tow dates DateField1 and DateField2 and find number of hours between these two dates. Then I need to deduct non-business days and hours (Business days: Monday-Friday and Business Hours: 7:00am-7:00pm) from this and find net hours. How can I do this?
View 7 Replies
View Related
Mar 10, 2008
Hi,
I have two varchar column in SQL.there im storing Month name
Lets say,
col1 col2
JAN APR
MAY DEC
Is there any possibility to use the sql query select * from tablename where DEC between (col1,col2)
I just wonder since it is varchar field and not date field can i use this syntax to compare ? or any other better way to achieve this?
Thanks for any response.
Regards,
RR
View 1 Replies
View Related
Apr 13, 2008
Hi,I need to compare Todays Date with a Date in the Table (DateExpired)to see if the membership expired. How do I do that? thanks
SELECT DateExpired FROM Member
View 2 Replies
View Related
May 14, 2008
i am making a transaction function for my website, when people transfer an amount from account A to account B, it should check the amount if account A has enough money to transfer. here is my codes, should i add an if statement before the tra.update()? how to write this codes? SqlDataSource tra = new SqlDataSource(); tra.ConnectionString = ConfigurationManager.ConnectionStrings["shuliConnectionString"].ToString(); tra.UpdateCommand = "update account set balance=balance" + -Convert.ToDecimal(TextBox5.Text) + " where AccountNumber = '" + TextBox3.Text + "'"; tra.Update(); tra.UpdateCommand = "update account set balance=balance+" + Convert.ToDecimal(TextBox5.Text) + "where AccountNumber = '" + TextBox4.Text + "'"; tra.Update(); tra.InsertCommand = "insert into Transactions(TransactionType,AccountNumber,DestAccount,Amount,Comment,ModifyDate) values ('T','" + TextBox3.Text + "','" + TextBox4.Text + "','" + TextBox5.Text + "','" + TextBox2.Text + "','" + DateTime.Now.ToLocalTime() +"')"; int inertRowNum = tra.Insert(); Server.Transfer("deposit_withdraw_confirm.aspx");
View 5 Replies
View Related
