Compare database schema in SQL server

Try this

http://weblogs.sqlteam.com/jeffs/archive/2004/11/10/2737.aspx[^]

There is no easy way to do this using stored procedure or query. You can try querying sys schema that contains all meta data about database.

For example you can get object definition for stored procedures like this

select OBJECT_SCHEMA_NAME(P.object_id) as [Schema], P.name as [Procedure Name] ,c.text as Procedure_Definition
from sys.procedures P
inner join sys.syscomments C on P.object_id = C.id

Using this query you can join two database tables and select those where database1.sys.syscomments.text is not qual to database1.sys.syscomments.text

Another option is to use a third party tool such as ApexSQL Diff or any similar tool on the market.

By "compare" perhaps you mean "find a short cut avoiding nested SELECT"?

USE [database]
GO
SELECT * FROM INFORMATION_SCHEMA.SCHEMATA

Shows CATALOG_NAME & SCHEMA_NAME. Having those two bits of info makes a WHERE clause more useful. Especially since the SCHEMA is named. Xreference sys.objects or other sys. procedures in an EXEC clause. Etc.

Also quite a few other "View" INFORMATION_SCHEMA items to oconnsider. See BOL.