DBA管理脚本大全

一：基于sqlserver 数据库段，连接 mysql，oracle数据库采用数据库链接方式

GO

--功能：对比多数据库之间的结构关系异同，方便DBA统一管理

--日期：2013-02-13

--调用:

--EXEC COMPARE_DBTODB '','OPENVONE',2,'MYSQL1','OPENVONE',3

--EXEC COMPARE_DBTODB 'ORACLE2','OPENVONE',3,'','OPENVONE',2

--EXEC COMPARE_DBTODB 'ORACLE2','OPENVONE',1,'MYSQL1','OPENVONE',3

CREATE PROCEDURE [dbo].[COMPARE_DBTODB]

(

@SOURCELINK VARCHAR(50), --源链接服务器名称,为空则表示为本机,由于采用SQLSERVER中转，只有SQLSERVER才能在本机查询，其他均为链接查询

@SOURCEDB VARCHAR(50), --源数据库名/用户名

@SOURCETYPE VARCHAR(50), --源数据库类型 1-ORACLE, 2-MSSQL,3-MYSQL

@TARGETLINK VARCHAR(50), --目标链接服务器名称,为空则表示为本机

@TARGETDB VARCHAR(50), --目标数据库名/用户名

@TARGETTYPE INT --目标数据库类型 1-ORACLE, 2-MSSQL,3-MYSQL

)

AS

BEGIN

DECLARE

@ORACLETEXT VARCHAR(4000),

@MSSQLTEXT VARCHAR(4000),

@MYSQLTEXT VARCHAR(4000),

@SOURCETEXT VARCHAR(4000),

@TARGETTEXT VARCHAR(4000),

@SQLTEXT VARCHAR(4000)

--ORACLE结构查询语句

SET @ORACLETEXT=N'SELECT A.TABLE_NAME AS TABLENAME,B.COLUMN_NAME AS COLUMNNAME,

CASE DATA_TYPE

WHEN ''''NUMBER'''' THEN

CASE NVL(TO_CHAR(B.DATA_SCALE),''''0'''')

WHEN ''''0'''' THEN ''''INT''''

ELSE ''''DECIMAL(''''||NVL(TO_CHAR(B.DATA_PRECISION),''''18'''')||'''',''''||B.DATA_SCALE||'''')''''

END

WHEN ''''VARCHAR2'''' THEN ''''VARCHAR(''''||B.CHAR_LENGTH||'''')''''

WHEN ''''DATE'''' THEN ''''DATETIME''''

WHEN ''''BLOB'''' THEN ''''VARBINARY(4000)''''

WHEN ''''LONG'''' THEN ''''VARCHAR(4000)''''

ELSE B.DATA_TYPE

END AS SQLTYPE,

CASE DATA_TYPE

WHEN ''''NUMBER'''' THEN REPLACE(B.DATA_TYPE||''''(''''||NVL(TO_CHAR(B.DATA_PRECISION),''''*'''')||'''',''''||NVL(TO_CHAR(B.DATA_SCALE),''''*'''')||'''')'''',''''(*,*)'''','''''''')

WHEN ''''VARCHAR2'''' THEN B.DATA_TYPE||''''(''''||B.CHAR_LENGTH||'''' BYTE)''''

ELSE B.DATA_TYPE

END AS ALLTYPE,

CASE B.NULLABLE WHEN ''''Y'''' THEN ''''NULL'''' ELSE ''''NOT NULL'''' END AS ISNULLABLE,

COLUMN_ID AS COLUMN_ID

from dba_tables A JOIN DBA_TAB_COLUMNS B ON (A.TABLE_NAME=B.TABLE_NAME AND A.OWNER=B.OWNER)

WHERE A.OWNER=''''''@DBOWNER'''''''''

--SQLSERVER结构查询语句

SET @MSSQLTEXT=N'SELECT TABLENAME,COLUMNNAME,SQLTYPE,SQLTYPE AS ALLTYPE,ISNULLABLE,COLUMN_ID FROM(

SELECT A.NAME AS TABLENAME,b.name AS COLUMNNAME,UPPER(c.NAME)+

replace(CASE c.name

WHEN ''''bigint'''' THEN ''''''''

WHEN ''''binary'''' THEN ''''(''''+CONVERT(VARCHAR,B.PREC)+'''')''''

WHEN ''''bit'''' THEN ''''''''

WHEN ''''char'''' THEN ''''(''''+CONVERT(VARCHAR,B.PREC)+'''')''''

WHEN ''''datetime'''' THEN ''''''''

WHEN ''''decimal'''' THEN ''''(''''+CONVERT(VARCHAR,B.PREC)+'''',''''+CONVERT(VARCHAR,B.SCALE)+'''')''''

WHEN ''''float'''' THEN ''''''''

WHEN ''''image'''' THEN ''''''''

WHEN ''''int'''' THEN ''''''''

WHEN ''''money'''' THEN ''''''''

WHEN ''''nchar'''' THEN ''''(''''+CONVERT(VARCHAR,B.PREC)+'''')''''

WHEN ''''ntext'''' THEN ''''''''