RE:Comparar estructuras de Bases de Datos
Independientemente de que este no es el foro de ADO, ni VB... intentaré ayudarte con un procedimiento que cree hace años en SQL puro y duro.
Si te sirve, adáptalo a ADO.
Está implementado para Sybase. Sólo cambian un par de cosas en la parte del cursor para que lo adaptes a SQL Server.
--
-- Descripción:
-- Procedimiento que genera información de CREATE TABLE de todas las tablas
-- de una base de datos determinada en la conexión isql.
--
SET NOCOUNT ON
go
DECLARE Cursor_Estructura CURSOR FOR
SELECT A.name AS Tabla
, B.colid AS NCampo
, B.name AS DS_Campo
, C.name AS TipoDato
, B.length AS Length
, B.prec AS Prec
, B.scale AS Scale
, B.status AS AllowNulls
FROM sysobjects AS A LEFT OUTER JOIN
syscolumns AS B ON A.id = B.id LEFT OUTER JOIN
systypes AS C ON B.type = C.type
AND B.usertype = C.usertype
WHERE A.type = 'U'
ORDER BY 1,2
FOR READ ONLY
go
DECLARE @Tabla VARCHAR(255)
, @NCampo INT
, @DS_Campo VARCHAR(255)
, @TipoDato VARCHAR(255)
, @Length INT
, @Prec INT
, @Scale INT
, @AllowNulls INT -- 1 = null; 0 = not null
DECLARE @String NVARCHAR(255)
, @TablaLee VARCHAR(255)
SELECT @String = ''
OPEN Cursor_Estructura
FETCH Cursor_Estructura INTO @Tabla, @NCampo, @DS_Campo, @TipoDato
, @Length, @Prec, @Scale, @AllowNulls
SELECT @TablaLee = @Tabla
WHILE (@@sqlstatus = 0)
BEGIN
IF (@String = '')
BEGIN
SELECT @String = 'IF EXISTS(SELECT * FROM sysobjects WHERE type = "U" AND name = "' + RTRIM(LTRIM(@Tabla)) + '")'
PRINT @String
SELECT @String = 'DROP TABLE ' + RTRIM(LTRIM(@Tabla))
PRINT @String
PRINT 'go'
SELECT @String = 'CREATE TABLE ' + RTRIM(LTRIM(@Tabla)) + ' ( '
PRINT @String
SELECT @String = CASE WHEN RTRIM(LTRIM(@DS_Campo)) = 'function' THEN 'funcion'
ELSE RTRIM(LTRIM(@DS_Campo))
END + ' ' +
CASE WHEN @TipoDato IS NULL or CHAR_LENGTH(RTRIM(LTRIM(@TipoDato)))=0 THEN
CASE WHEN @Length > 4 THEN 'char'
WHEN UPPER(RIGHT(@DS_Campo,3)) = 'FLG' THEN 'char'
WHEN (@DS_Campo LIKE '%text%') THEN 'char'
ELSE 'int'
END
ELSE @TipoDato
END +
CASE WHEN @TipoDato LIKE '%char%'
THEN '('+CONVERT(VARCHAR(255), @Length) + ')'
WHEN @TipoDato LIKE '%binary%'
THEN '('+CONVERT(VARCHAR(255), @Length) + ')'
WHEN @TipoDato LIKE '%numeric%'
THEN '('+CONVERT(VARCHAR(255), @Prec) + ',' + CONVERT(VARCHAR(255), @Scale) + ')'
WHEN (@TipoDato IS NULL or CHAR_LENGTH(RTRIM(LTRIM(@TipoDato)))=0)
AND UPPER(RIGHT(@DS_Campo,3)) = 'FLG'
THEN '(1)'
WHEN (@TipoDato IS NULL or CHAR_LENGTH(RTRIM(LTRIM(@TipoDato)))=0)
AND (@DS_Campo LIKE '%text%')
THEN '('+CONVERT(VARCHAR(255), @Length) + ')'
WHEN (@TipoDato IS NULL or CHAR_LENGTH(RTRIM(LTRIM(@TipoDato)))=0)
AND (@Length > 4)
THEN '('+CONVERT(VARCHAR(255), @Length) + ')'
ELSE ''
END +
CASE WHEN @AllowNulls & 128 <> 0 THEN ' IDENTITY '
WHEN @AllowNulls & 8 = 0 THEN ' NOT NULL '
ELSE ' NULL ' END
PRINT @String
END
FETCH Cursor_Estructura INTO @Tabla, @NCampo, @DS_Campo, @TipoDato
, @Length, @Prec, @Scale, @AllowNulls
IF (@TablaLee <> @Tabla)
BEGIN
SELECT @String = ' )'
PRINT @String
PRINT 'go'
SELECT @TablaLee = @Tabla
SELECT @String = ''
END
ELSE
BEGIN
SELECT @String = ", " + CASE WHEN RTRIM(LTRIM(@DS_Campo)) = 'function' THEN 'funcion'
ELSE RTRIM(LTRIM(@DS_Campo))
END + ' ' +
CASE WHEN @TipoDato IS NULL or CHAR_LENGTH(RTRIM(LTRIM(@TipoDato)))=0 THEN
CASE WHEN @Length > 4 THEN 'char'
WHEN UPPER(RIGHT(@DS_Campo,3)) = 'FLG' THEN 'char'
WHEN (@DS_Campo LIKE '%text%') THEN 'char'
ELSE 'int'
END
ELSE @TipoDato
END +
CASE WHEN @TipoDato LIKE '%char%'
THEN '('+CONVERT(VARCHAR(255), @Length) + ')'
WHEN @TipoDato LIKE '%binary%'
THEN '('+CONVERT(VARCHAR(255), @Length) + ')'
WHEN @TipoDato LIKE '%numeric%'
THEN '('+CONVERT(VARCHAR(255), @Prec) + ',' + CONVERT(VARCHAR(255), @Scale) + ')'
WHEN (@TipoDato IS NULL or CHAR_LENGTH(RTRIM(LTRIM(@TipoDato)))=0)
AND UPPER(RIGHT(@DS_Campo,3)) = 'FLG'
THEN '(1)'
WHEN (@TipoDato IS NULL or CHAR_LENGTH(RTRIM(LTRIM(@TipoDato)))=0)
AND (@DS_Campo LIKE '%text%')
THEN '('+CONVERT(VARCHAR(255), @Length) + ')'
WHEN (@TipoDato IS NULL or CHAR_LENGTH(RTRIM(LTRIM(@TipoDato)))=0)
AND (@Length > 4)
THEN '('+CONVERT(VARCHAR(255), @Length) + ')'
ELSE ''
END +
CASE WHEN @AllowNulls & 128 <> 0 THEN ' IDENTITY '
WHEN @AllowNulls & 8 = 0 THEN ' NOT NULL '
ELSE ' NULL ' END
IF (@@sqlstatus = 0)
PRINT @String
END
END -- Del WHILE
SELECT @String = ' )'
PRINT @String
PRINT 'go'
CLOSE Cursor_Estructura
go
DEALLOCATE CURSOR Cursor_Estructura
go