1-) MSSQL RMOS - iki(2) veri tabanında farklı tabloları bulmak
select 'RmosOrderTaker' as dbname, t1.table_name
from RmosOrderTaker.[INFORMATION_SCHEMA].[tables] t1
where table_name not in (select t2.table_name
from
Demo01OrderTaker.[INFORMATION_SCHEMA].[tables] t2
)
union
select 'Demo01OrderTaker' as dbname, t1.table_name
from Demo01OrderTaker.[INFORMATION_SCHEMA].[tables] t1
where table_name not in (select t2.table_name
from
RmosOrderTaker.[INFORMATION_SCHEMA].[tables] t2
)
2-) tüm tablo ve columnları listeleme
SELECT
[table].TABLE_NAME AS [Table_Name],
[column].COLUMN_NAME AS [Column_Name],
COLUMNPROPERTY(object_id([table].[TABLE_NAME]), [column].[COLUMN_NAME], 'IsIdentity') AS [identity],
[column].DATA_TYPE AS [datatype],
[column].CHARACTER_MAXIMUM_LENGTH AS [Character_Length],
[column].NUMERIC_PRECISION AS Numeric_precision,
[column].ORDINAL_POSITION AS [order],
[column].COLUMN_DEFAULT AS [defaultvalue],
[column].IS_NULLABLE AS [nullable]
FROM
Demo01OrderTaker.INFORMATION_SCHEMA.TABLES [table] INNER JOIN
Demo01OrderTaker.INFORMATION_SCHEMA.COLUMNS [column] ON [table].TABLE_NAME = [column].TABLE_NAME
WHERE
[table].TABLE_TYPE = 'BASE TABLE'
AND [table].TABLE_NAME <> 'sysdiagrams'
ORDER BY
[table].TABLE_NAME ASC,
[column].ORDINAL_POSITION ASC
SADECE VERİ TABANI İSMİ DEĞİŞMİŞ AŞAĞIDAKİ
SELECT
[table].TABLE_NAME AS [Table_Name],
[column].COLUMN_NAME AS [Column_Name],
COLUMNPROPERTY(object_id([table].[TABLE_NAME]), [column].[COLUMN_NAME], 'IsIdentity') AS [identity],
[column].DATA_TYPE AS [datatype],
[column].CHARACTER_MAXIMUM_LENGTH AS [Character_Length],
[column].NUMERIC_PRECISION AS Numeric_precision,
[column].ORDINAL_POSITION AS [order],
[column].COLUMN_DEFAULT AS [defaultvalue],
[column].IS_NULLABLE AS [nullable]
FROM
RmosOrderTaker.INFORMATION_SCHEMA.TABLES [table] INNER JOIN
RmosOrderTaker.INFORMATION_SCHEMA.COLUMNS [column] ON [table].TABLE_NAME = [column].TABLE_NAME
WHERE
[table].TABLE_TYPE = 'BASE TABLE'
AND [table].TABLE_NAME <> 'sysdiagrams'
ORDER BY
[table].TABLE_NAME ASC,
[column].ORDINAL_POSITION ASC
3-) İKİ VERİ TABANINDA HANGİ COLUMNLARIN FARKLI OLDUĞUNU BULMA
begin
SELECT
[table].TABLE_NAME AS [Table_Name],
[column].COLUMN_NAME AS [Column_Name],
COLUMNPROPERTY(object_id([table].[TABLE_NAME]), [column].[COLUMN_NAME], 'IsIdentity') AS [identity],
[column].DATA_TYPE AS [datatype],
[column].CHARACTER_MAXIMUM_LENGTH AS [Character_Length],
[column].NUMERIC_PRECISION AS Numeric_precision,
[column].ORDINAL_POSITION AS [order],
[column].COLUMN_DEFAULT AS [defaultvalue],
[column].IS_NULLABLE AS [nullable] into demoorder
FROM
Demo01OrderTaker.INFORMATION_SCHEMA.TABLES [table] INNER JOIN
Demo01OrderTaker.INFORMATION_SCHEMA.COLUMNS [column] ON [table].TABLE_NAME = [column].TABLE_NAME
WHERE
[table].TABLE_TYPE = 'BASE TABLE'
AND [table].TABLE_NAME <> 'sysdiagrams'
ORDER BY
[table].TABLE_NAME ASC,
[column].ORDINAL_POSITION ASC
--***
SELECT
[table].TABLE_NAME AS [Table_Name],
[column].COLUMN_NAME AS [Column_Name],
COLUMNPROPERTY(object_id([table].[TABLE_NAME]), [column].[COLUMN_NAME], 'IsIdentity') AS [identity],
[column].DATA_TYPE AS [datatype],
[column].CHARACTER_MAXIMUM_LENGTH AS [Character_Length],
[column].NUMERIC_PRECISION AS Numeric_precision,
[column].ORDINAL_POSITION AS [order],
[column].COLUMN_DEFAULT AS [defaultvalue],
[column].IS_NULLABLE AS [nullable] into rmosorder
FROM
RmosOrderTaker.INFORMATION_SCHEMA.TABLES [table] INNER JOIN
RmosOrderTaker.INFORMATION_SCHEMA.COLUMNS [column] ON [table].TABLE_NAME = [column].TABLE_NAME
WHERE
[table].TABLE_TYPE = 'BASE TABLE'
AND [table].TABLE_NAME <> 'sysdiagrams'
ORDER BY
[table].TABLE_NAME ASC,
[column].ORDINAL_POSITION ASC
--SONRASINDA ...
select* from demoorder d where not exists
(
select * from rmosorder r
where d.Column_Name=r.Column_Name
)
drop table rmosorder
drop table demoorder
end
3-) İKİ VERİ TABANINDA FARKLI OLAN PROJEDURELERİ BULMA
BURADA DIKKAT ETMEN GEREKEN 2 PROCEDUREDEN BOŞUKLARI TABLARI SİL OYLE EŞİTMİ DİYE BAK
select S1.name [Db1_Schema], O1.name as [Db1_Object], O1.modify_date,
S2.name [Db1_Schema], O2.name as [Db1_Object], O2.modify_date,C1.text
from Demo01OrderTaker.sys.all_objects O1
inner join RmosOrderTaker.sys.all_objects O2 on O1.name = O2.name
inner join Demo01OrderTaker.sys.syscomments C1 on O1.object_id = C1.id
inner join RmosOrderTaker.sys.syscomments C2 on O2.object_id = C2.id
inner join Demo01OrderTaker.sys.schemas S1 on O1.schema_id = S1.schema_id
inner join RmosOrderTaker.sys.schemas S2 on O2.schema_id = S2.schema_id
where C1.text <> C2.text and
-- remove the line below if you want to search all objects
O1.type = 'P'