* 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