Hi,
I would like to compare an object inside a local and remote db and I would like see if any changes has been made to the object structure (table) since the last update.
Thanks
Hi everybody
I want to compare structure of two database
how could I do it?
Thanks
Mokhtari
I have one databaseand I create new database by copy everything from first db I then alter some column on new database and now I would like to compare two databaseHow can I do it ?
View 4 Replies View RelatedIs there a stored procedure to allow me to compare twoSQL objects? In my case for example I want to comparetwo stored procedures on two different databases?If there is no SP that does comparison, would there beany code in SQL DMO that does this?How does someone learn SQL DMO? Does SQL Server2000 have by default DMO learning material or I have tosearch for books?Thank you
View 3 Replies View RelatedI was wondering if there's a way to compare 1 table with other (in other DB), I mean:
Table1 DB1
ID
NAME
DESCRIPTION
Table1 DB2
ID
NAME
DESCRIPTION
ADDRESS.
And when the query(or other case) gets that the table 1 doesn't contain ADDRESS, alter the table and add this field that is missing.
Hi,
I want to compare the production (say prod_db) objects against development database say dev_db.
In Oracle I would run a query using "minus" that is
select table_name from user_tables@prod_db
minus
select table_name from
user_tables@dev_db
what is the equivalent sql in t-sql(or are there any GUI tools to do this).I understand that from prod_db I need to create a link server to the dev_db to run such a query.
Thanks,
copernicus.
Is there a way to compare two databases one being an olddatabase and the second being a new database, I willupdate the old database's objects to match the new database'sobjects?Is there SQL code that could do this easily?How do you do this if you deal with the same scenario?Thank you
View 2 Replies View RelatedI have two identical table structure with at least 60 or more columns!!
Table A
ID numeric(18, 0) Primary Key,
RefID numeric(18, 0),
CoNum Varchar(15),
PrevCode Varchar(15),
DispCode VArchar(15),
EffDate Date,
Sal numeric(18, 0),
SDeposit numeric(18, 0) Etc....
I need to compare Data between two table and provide result like this!
RefId ID, CoNum, PrevCode, DispCode, EffDate, Sal, SDeposit
1, ,No Match, ,20,5200,0
So condition like. If table A Column Data does not Equal Table B Column data then
If datatype is Date then display Difference of Days.
if Datatype is Numeric then display Difference between value.( it display 0 if doesn't have difference)
if Datatype is Varchar then display No Match word.
Is there an easy way to compare the contents of objects between 2 different databases? For example, say I had 2 databases, My_DB_1 and My_DB_2, each with a SEC_User table. Say I wanted to do an object-to-object comparison between databases to see if there were any differences. Here's some sample SQL:
use My_DB_1
select * from sys.dm_sql_referencing_entities('dbo.sec_user', 'object')
use My_DB_2
select * from sys.dm_sql_referencing_entities('dbo.sec_user', 'object')
Say that the result sets above both returned a SEC_GetUser sproc object ref. Is there a way to write SQL that will compare the SEC_GetUser sprocs (and other objects in the above rowsets) from both databases? For example, if My_DB_1.SEC_GetUser returns an extra column in the result set than My_DB_2.SEC_GetUser then I'd like my comparison SQL to return a single column "IsDifferent" with SEC_GetUser as a row....
please help newbieI need to create a lot of objects the same type (let's say: schemas)I wish to use paramerized block in loop to do so.- how to put names of my objects to such control-flow?belss you for help
View 5 Replies View Relatedpassing serialised objects to a stored procedure for the purpose of data inserts. I see this as being a way to handle multiple row inserts efficiently.
However, in my limited use of XML data I am not so sure how to link the data when I have a dependency on another "object" within the serialised XML.
Below is a code snippet showing what I have so far.
The first insert statement works fine - but how to retrieve the identifier created by the DB - I want to use an SQL statement that finds the record in the table based on the XML representation (of the PluginInfo), allowing me to insert the ConfigurationInfo with the correct reference to the PluginInfo
DECLARE @Config NVARCHAR(MAX)
DECLARE @Handle AS INT
DECLARE @TransactionCount AS INT
SELECT @Config = '
<ConfigurationDirectory >
<ConfigurationInfo groupKey="Notifications" sectionKey="App.Customization.PluginInfo"
[code]....
Hi,I'm using DB2 UDB 7.2.Also I'm doing some tests on SQL Server 2000 for some statements touse efectively.I didn't find any solution on Sql Server about WITH ... SELECTstructure of DB2.Is there any basic structure on Sql Server like WITH ... SELECTstructure?A Sample statement for WITH ... SELECT on DB2 like belowWITHtotals (code, amount)AS (SELECT code, SUM(amount) FROM trans1 GROUP BY codeUNION ALLSELECT code, SUM(amount) FROM trans2 GROUP BY code)SELECTcode, SUM(amount)FROM totalsGROUP BY code.............................Note: 'creating temp table and using it' maybe a solution.However i need to know the definition of the result set of Unionclause. I don't want to use this way.CREATE TABLE #totals (codechar(10), amount dec(15))GOINSERT INTO #totalsSELECT code, SUM(amount) FROM trans1 GROUP BY codeUNION ALLSELECT code, SUM(amount) FROM trans2 GROUP BY codeGOSELECT code, sum(amount) FROM #totals GROUP BY codeGOAny help would be appreciatedThanks in advanceMemduh
View 3 Replies View RelatedTable 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
let me explain my business rules first.
One company can have many site addresses;
One company can have many trades
And one trade can have many activities.
Ok seems very basic at this stage does'nt it, but if i give some data the complexity changes completly for example:
the trades are as follows:
TRADE TABLE
ID 1: DESC: Printing
ID 2: DESC: Artwork
ID 3: DESC: Photography
ID 4: DESC: Reprographics.
now in this case only the printing trade has activities:
TRADE ACTIVITIES TABLE
ID 1: DESC: Flexo
ID 2: DESC: Digital
So, in the above i am saying which ever company picks Printing as there trade, then they have the option of specifying which particular printing trade it is that they do.
Again simple enough by using one-to-many from the Trade Table to the Trade Activity table.
The problem is that not all trades have associated trade activities, for example the artwork trade activity is just artwork, but i need to be able to allow this to be so in the future.
When I try to create a view to select a particular company, with there associated trade and trade activity, I do not get any result at all because not all of the trade activities have any records assigned to them.
Please can anyone help.
I need to be able to pull back all companies with their associated trades and associated trade activities.
Cheers
Wayne
Hi All
I want the structure of the entire database without the data from a database , I mean all the tables withouut the data ... Can i do that
Plz help
As my learning process continues, I have found that Database structure is probably the most important & most difficutl part of development. Like database normallization etc.
How can I master database structure, ? but I want learning other than any database software like Access or SQL Server, I m looking for a generallized learning.
Will some1 give me assistance ...
Hi,
is there a way of exporting a database structure and not the data in it?
Thanks.
Hello I am a final year student and for my final year project I have decided to try and create a price comparison website due to the fact that i feel it would be a good project to work on and develop my technical skills in C# and microsoft sql server 2005, since these are mainly the two programs I would say i have more experience with. Basically I want to create a website such as www.pricerunner.com. At this point in time i have started playing around with how i feel the database will look, in terms of tables and columns, below is what i have produced;
Company
Company_ID
Company_name
ADDRESS
Website link
contact
Products
Product_ID
Product_name
Product_Category
Product_Price
I am now stuck as how i will link each company with a product bearing in mind a company will stock different categories of products, also do you think i need any more columns in any of the above tables, as well as am i missing out on anything, I am hoping someone with more knowlegde than myself can shed some knowledge on my problem, thank you.
Structure Number1
Category: CategoryId(P.K.), UniqueName, CreatedDate, ModifiedDateCategoryNative: CategoryId(F.K.), LanguageId(F.K.), NativeName, Description, Importance, IsVisible, CreatedDate, ModifiedDate
Structure Number2
Category: CategoryId(P.K.), UniqueName, CreatedDate, ModifiedDateCategoryNative: CategoryNativeId(P.K.), CategoryId(F.K.), LanguageId(F.K.), NativeName, Description, Importance, IsVisible, CreatedDate, ModifiedDate
Can anyone tell me that between above 2 structure what is better and why?
I just add CategoryNativeId(P.K.) in Number 2 structure.
Dear all,
how can I use a store procedure to get the structure of a table like data type, length, description etc.
I have used sp_columns to get the information but it can't get the description of each field.
Thanks.
I'm sure there is a simple way to do this but I cannot think of it.I have an ASP.NET application that I am publishing to a webhost. I have chosen to use FTP to copy my files (aspx, etc.). They work as far as links etc. Now, the next step is to create my SQL database on their server as they instruct, which I have done in name only. (Oh, if it makes a difference, I access the hosting service using Plesk control panel.) Is there any simple way to get my database structure from my PC to the host server? All responses welcome. Thanks.
View 5 Replies View RelatedOur company would like to start using SQL 7.0 Snapshot replication for some of our customers. My problem is that we often have to make changes to the actual structure of the DB. Will I need to disable replication each time we need to make a change to the db structure on the publisher and subscribers?? Thank you...
View 1 Replies View RelatedHi All,
I need to get some info on how I can get the script of the entire database stucture. I need to write a procedure to do this. I know in SQL Server I could get the script in a mouse click. But I need to write a procedure to do the task. Is there a package in SQL Server that I could use to accomplish this task?
Any thoughts will be of help.
Thanks in advance.
sj
Could anybody tell me the detail structure of the .mdf file.
Actully I want to read the mdf file with out using MS SQL 2000.
I know few details like it has 96 bytes header and pages are divided in 8Kb etc. But I want to know the exact details, so that I can read the Table names and columns at least.
Thanks in advance,
Prasanna.
I am very new to SQL server, and am completely stumped here. I need to transfer the data from an old database to a new one (both SQL Server 6.5 DBs.) The only problem is that the database has been restructured, and I can't for the life of me figure out how to get the data imported. I am working on manually making all of the structure changes, but there must be a better way. Any ideas? Thank you SO much!
Jeff
When you attach an MDF that was created using SQL 7.0 to SQL Server 2000, the MDF file structure gets updated. The resulting MDF can no longer be used by SQL 7.0.
My Question: does anyone know how to read the file structure version of a detached MDF file?
If the file structure is 7.0, I would like to warn the user and allow her to cancel the attach + conversion to SQL 2000.
Thanks!
I a DB deployed in the field at numerous customer sites. I have made changes
to the DB in my dev environment and now I need a way to update the DBs out in
the field to the new DB structure. Examples of updates are adding a new column
to a table, or adding some more code to a stored proc.
So what do I do ? I need to be able to email comething to the customer that
they can run that will make my recent updates to their DB structure. It can't
screw up their existing data either.
So what's the solution ? A big SQL script wil ALTER TABLE commands ?
How do I updated stored procs through a script ? I need help seriously.
What is the accepted industry standard method of updating deployed DBs
when a new version is released ? Please Please help.
Thanks in advance
Josh
I have 2 databases with corresponding history databases.
As the system is under development, the 2 databases are changing in structure i.e the table structure(new tables as well as changes to existing ones) and indexes will change every week.
What is the best solution to replicate the changes on the history database.
Regards,
Ashutosh
How can I get the structure of a table?
With MySql I can do it this way: DESC | DESCRIBE <tablename>
Thank you,
Dirk
Dirk.Ulrich@gmx.de
This is a sort of database structure question that could I guess apply to any type of database...
Say I have 4 production lines and I want to log serial numbers on the start of every line and off the end of every line.
Is there any advantage in having a separate table for each Production Line and serial number logging point? (i.e. t_Line1Start, t_Line1End, t_Line2Start etc) or is it just the same as having one big table and having a LineNumber field and a StartEnd field?
Is SQL Server just as happy dealing with fewer transactions to several tables as it is to many transactions to one table?
Hi,
I have a question. I want to build a db for the following entities:
drivers
cars
companies
each driver nay has a car (NULL or carId)
each driver belongs to company
each car belongs to company
The db structure I built is:
car(carId, companyId)
driver(driverId, companyId, carId)
company(companyId)
The problem is that in this structure driver from company a can have a car from company b.
but this situation is not possible.. How can I prevent such situation??
tnx
How can I setup a table structure for the diagram shown in the attached bitmap?
1) I need to create a product using labour and materials.
2) I need to create hardware using labour and materials.
3) I need to be able to include some hardware in some products.
4) Some materials in the product are made of a culmination of materials. E.G., Concrete is made of sand, stone, and cement.
Any suggestions?
So far I have :
USE NORTHWIND
Create Table tbProducts (
ProductID int NOT NULL,
Product varchar(50)
)
go
ALTER TABLE tbProducts
ADD CONSTRAINT tbProducts_pk PRIMARY KEY (ProductID)
GO
CREATE Table tbMaterials (
MaterialID int NOT NULL,
Material varchar (50)
)
GO
ALTER TABLE tbMaterials
ADD CONSTRAINT tbMaterials_pk PRIMARY KEY (MaterialID)
GO
CREATE Table tbLabour (
LabourCode char (2) NOT NULL,
Labour varchar(50)
)
GO
ALTER TABLE tbLabour
ADD CONSTRAINT tbLabour_pk PRIMARY KEY (LabourCode)
GO
CREATE Table tbProductMaterials (
fkProductID int NOT NULL,
fkMaterialID int NOT NULL,
Quantity Float NOT NULL
)
GO
ALTER TABLE tbProductMaterials
ADD CONSTRAINT tbProductMaterials_pk PRIMARY KEY (fkProductID, fkMaterialID)
GO
ALTER TABLE tbProductMaterials
ADD CONSTRAINT tbProductMaterils_fk FOREIGN KEY (fkMaterialID)
REFERENCES tbMaterials (MaterialID)
GO
ALTER TABLE tbProductMaterials
ADD CONSTRAINT tbProductMaterils_Product_fk FOREIGN KEY (fkProductID)
REFERENCES tbProducts (ProductID)
GO
CREATE TABLE tbProductLabour (
fkProductID int NOT NULL,
fkLabourCode char(2) NOT NULL,
Manpower int NULL DEFAULT(0),
Hours float NULL DEFAULT(0.0)
)
GO
ALTER TABLE tbProductLabour
ADD CONSTRAINT tbProductLabour_pk PRIMARY KEY (fkProductID, fkLabourCode)
GO
ALTER TABLE tbProductLabour
ADD CONSTRAINT tbProductLabour_fk FOREIGN KEY (fkLabourCode)
REFERENCES tbLabour (LabourCode)
GO
ALTER TABLE tbProductLabour
ADD CONSTRAINT tbProductLabour_Product_fk FOREIGN KEY (fkProductID)
REFERENCES tbProducts (ProductID)
GO
CREATE TABLE tbHardware (
HardwareID int NOT NULL,
Hardware varchar(50)
)
go
ALTER TABLE tbHardware
ADD CONSTRAINT tbHardware_pk PRIMARY KEY (HardwareID)
GO
CREATE Table tbHardwareMaterials (
fkHardwareID int NOT NULL,
fkMaterialID int NOT NULL,
Quantity Float NOT NULL
)
GO
ALTER TABLE tbHardwareMaterials
ADD CONSTRAINT tbHardwareMaterials_pk PRIMARY KEY (fkHardwareID, fkMaterialID)
GO
ALTER TABLE tbHardwareMaterials
ADD CONSTRAINT tbHardwareMaterials_fk FOREIGN KEY (fkMaterialID)
REFERENCES tbMaterials (MaterialID)
GO
ALTER TABLE tbHardwareMaterials
ADD CONSTRAINT tbHardwareMaterials_Hardware_fk FOREIGN KEY (fkHardwareID)
REFERENCES tbHardware (HardwareID)
GO
CREATE TABLE tbHardwareLabour (
fkHardwareID int NOT NULL,
fkLabourCode char(2) NOT NULL,
Manpower int NULL DEFAULT(0),
Hours float NULL DEFAULT(0.0)
)
GO
ALTER TABLE tbHardwareLabour
ADD CONSTRAINT tbHardwareLabour_pk PRIMARY KEY (fkHardwareID, fkLabourCode)
GO
ALTER TABLE tbHardwareLabour
ADD CONSTRAINT tbHardwareLabour_fk FOREIGN KEY (fkLabourCode)
REFERENCES tbLabour (LabourCode)
GO
ALTER TABLE tbHardwareLabour
ADD CONSTRAINT tbHardwareLabour_Product_fk FOREIGN KEY (fkHardwareID)
REFERENCES tbHardware (HardwareID)
GO
This table structure does not include anything about accounts and item 4) making materials from 1 or more other materials (E.G., Concrete).
Any hints how to incorporate these outstanding items?
Mike B
How can I get my table structure (columns, their types, whether it s an Identity column) using T-SQL
pls
Thanks you