scripst - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

scripst

Author

Topic

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2008-06-24 : 10:20:32

I need a scripts to compare the developement db and testing db databases tables strucutes..

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-24 : 10:23:59

I think you need to use Third party tools
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=91279

Madhivanan

Failing to plan is Planning to fail

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2008-06-24 : 10:31:34

Sorry i can install the third party tools..
Is there any scripts to compare the tables columns only not the dependenant objects or relationships keys..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-24 : 10:39:28

quote:
Originally posted by sqlfresher2k7

Sorry i can install the third party tools..
Is there any scripts to compare the tables columns only not the dependenant objects or relationships keys..

if you only need to compare the table columns only, just make use of INFORMATION_SCHEMA.COLUMNS

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-24 : 10:46:01

something like . .

SELECT	*
FROM	devdb.INFORMATION_SCHEMA.COLUMNS d
	left JOIN proddb.INFORMATION_SCHEMA.COLUMNS p	
	ON	d.TABLE_NAME	= p.TABLE_NAME
	AND	d.COLUMN_NAME	= p.COLUMN_NAME
WHERE	p.COLUMN_NAME	IS NULL

KH
[spoiler]Time is always against us[/spoiler]

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-06-24 : 12:04:46

there's also tablediff.exe, which ships with sql server, so you should already have it.

http://msdn.microsoft.com/en-us/library/ms162843.aspx

elsasoft.org

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-24 : 12:54:43

quote:
Originally posted by sqlfresher2k7

Sorry i can install the third party tools..
Is there any scripts to compare the tables columns only not the dependenant objects or relationships keys..

Check out the link I posted. You have other options as well

Madhivanan

Failing to plan is Planning to fail

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2008-06-24 : 13:31:50

Thanks a lot guys..

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2008-06-24 : 22:33:22

Can i get code for alter scripts to generate for the missing colums of table...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-25 : 01:43:09

quote:
Originally posted by sqlfresher2k7

Can i get code for alter scripts to generate for the missing colums of table...

Get the list of columns using query posted earier and then use

ALTER TABLE ADD <columnname>

to add them to table.

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2008-06-25 : 08:04:23

I need the scripts to generate alter scripts dynamically for the missing columns instead of manually doing the alter scripts...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-25 : 08:59:50

quote:
Originally posted by sqlfresher2k7

I need the scripts to generate alter scripts dynamically for the missing columns instead of manually doing the alter scripts...

you need to just get list of missing columns using earlier query into a variable inside a loop and build a sql string like

@Sql='ALTER Table ADD ' + @ColumnName

EXEC(@Sql)
and loop it for each column from list

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-06-25 : 09:23:15

I think tablediff.exe will generate the alter script for you.

elsasoft.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources