The latest project may be a product. There are several customers using it, and the database is also updated frequently,

Source: Internet
Author: User
* 1. Compare selectdistincta with different database tables. id, d. nameasTableName --,. nameasColumeNamefrom [onwerbuild_wjl2]. dbo. syscolumnsa, [onwerbuild_wjl2]. dbo. sysobjectsdwhered. xtypeUandd. namenotin (selectc. namefrom [onwerbuild_wjl_m

/* 1. Select distinct. id, d. name as TableName --,. name as ColumeName from [onwerbuild_wjl2]. dbo. syscolumns a, [onwerbuild_wjl2]. dbo. sysobjects d where d. xtype = 'U' and d. name not in (select c. name from [onwerbuild_wjl_m

/* 1. Different database tables
Select distinct a. id, d. name as TableName --, a. name as ColumeName
From [onwerbuild_wjl2]. dbo. syscolumns a, [onwerbuild_wjl2]. dbo. sysobjects d
Where d. xtype = 'U' and
D. name not in
(Select c. name from [onwerbuild_wjl_m]. dbo. syscolumns B, [onwerbuild_wjl_m]. dbo. sysobjects c where c. xtype = 'U' and B. id = c. id)
And a. id = d. id order by d. name

*/
-- 2. Compare database fields
-- Query the number of fields that database A has more than database B.
--
USE onwerbuild -- database

GO
-- Delete all data
-- Create a table
If exists (select * from dbo. sysobjects where id = object_id (n' [dbo]. [Test_del] ') and OBJECTPROPERTY (id, n'isusertable') = 1)
Drop table [dbo]. [Test_del]
GO

Create table [dbo]. [Test_del] (
[TableName] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL,
[ColumeName] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO


Delete from dbo. Test_del

DECLARE @ table_name varchar (50)
DECLARE abc CURSOR

-- Retrieve a table first
Select d. name as TableName
From [onwerbuild_AS]. dbo. sysobjects d -- database
Where d. xtype = 'U' order by d. name

OPEN abc

Fetch next from abc
Into @ table_name

WHILE (@ FETCH_STATUS = 0)
Begin
-- A table corresponds to a table
-- Insert different fields
USE onwerbuild_AS -- database
Insert into [onwerbuild]. dbo. Test_del
Select d. name as TableName, a. name as ColumeName
From [onwerbuild_AS]. dbo. syscolumns a, [onwerbuild_AS]. dbo. sysobjects d -- database
Where d. name = @ table_name
And a. name not in
(Select B. name from [onwerbuild]. dbo. syscolumns B, [onwerbuild]. dbo. sysobjects c where c. name = @ table_name and B. id = c. id) -- database B
And a. id = d. id order by d. name
Fetch next from abc
Into @ table_name

Set nocount on
End
CLOSE abc
DEALLOCATE abc

Select * from [onwerbuild]. dbo. Test_del
Select distinct TableName from [onwerbuild]. dbo. Test_del
GO

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.