Yesterday I was frustrated by the out-of-synchronization of the database. The libraries used for development and testing are not the same. In the library developed, the stored procedures are frequently updated. Sometimes even the table structure changes.
This leads to many problems. For a while, I didn’t know what the specific problem was, which made me very embarrassed. I suddenly wanted to write a program to compare the differences between the two libraries to facilitate testing and implementation.
This morning, I was writing while having a meeting, and passed the test at noon.
Mainly two SQL statements:
The SQL with different table and view structures and column attributes in the query library is as follows:
select A. [name] as TableName,
B. [name] as Colname,
B.xtype,
B.xusertype,
B.length,
B.colid,
B.cdefault,
B.domain,
B.number,
B.offset,
B.status,
B.type,
B.usertype,
B.prec,
B.scale,
B.iscomputed,
B.isoutparam,
B.isnullable,
C.COLUMN_DEFAULT,
dbo.fnIsColumnPrimaryKey(B. [ID], B. [name]) as PKey
from sysobjects A, syscolumns B, INFORMATION_SCHEMA .COLUMNS C
where a.id = B.id
and A.xtype in ('u', 'v')
and A. Name = C.TABLE_NAME
and B. Name = C.COLUMN_NAME
order by A. [ID], B. [Name]
///////
The meaning of each column is as follows
name sysname Column name or name of a procedure parameter.
id int The table object ID to which this column belongs, or the stored procedure ID associated with this parameter.
xtype tinyint Physical storage type in systypes.
typestat tinyint Internal use only.
xusertype smallint Extended user-defined data type ID.
length smallint Maximum physical storage length in systypes.
xprec tinyint Internal use only.
xscale tinyint Internal use only.
colid smallint column or parameter ID.
xoffset smallint Internal use only.
bitpos tinyint Internal use only.
reserved tinyint Internal use only.
colstat smallint Internal use only.
cdefault int The default value ID of this column.
domain int The rule or CHECK constraint ID of this column.
number smallint The sub-process number when the process is grouped (0 indicates non-process items).
colorder smallint Internal use only.
autoval varbinary(255) Internal use only.
offset smallint The offset of the row in which this column is located; if negative, indicates a variable-length row.
status tinyint Bitmap describing column or parameter attributes:
0x08 = Column allows null values.
0x10 = ANSI padding is in effect when adding a varchar or varbinary column. Preserves trailing spaces for varchar columns and trailing zeros for varbinary columns.
0x40 = The parameter is an OUTPUT parameter.
0x80 = Column is an identity column.
type tinyint The physical storage type in systypes.
usertype smallint User-defined data type ID in systypes.
printfmt varchar(255) Internal use only.
prec smallint The precision level of this column.
scale int Number of decimal places for this column.
iscomputed int Flag indicating whether the column has been calculated:
0 = not calculated.
1 = Calculated.
isoutparam int indicates whether the procedure parameter is an output parameter:
1 = true.
0 = false.
isnullable int indicates whether the column allows null values:
1 = true.
0 = false.
COLUMN_DEFAULT default value
PKey Primary key.
////////
The following functions are used:
--------------------------
CREATE FUNCTION dbo.fnIsColumnPrimaryKey(@sTableID int, @nColumnName varchar(128))
--alter FUNCTION dbo.fnIsColumnPrimaryKey(@sTableName varchar(128), @nColumnName varchar(128))
RETURNS bit
AS
BEGIN
DECLARE @nTableID int,
@nIndexID int,
@i int
SET @nTableID = @sTableID--OBJECT_ID(@sTableName)
SELECT @nIndexID = indid
FROM sysindexes
WHERE id = @nTableID
AND indid BETWEEN 1 And 254
AND (status & 2048) = 2048
IF @nIndexID Is Null
RETURN 0
IF @nColumnName IN
(SELECT sc.[name]
FROM sysindexkeys sik
INNER JOIN syscolumns sc ON sik.id = sc.id AND sik.colid = sc.colid
WHERE sik.id = @nTableID
AND sik.indid = @nIndexID)
BEGIN
RETURN 1
END
RETURN 0
END
--------------------------
Through this SQL statement, you can find out the column attributes of all tables in the database.
By executing this SQL statement twice respectively, the attributes of the columns in the two libraries can be retrieved and compared.
-------------------------------------------------- ---------------
For stored procedures and functions use the following SQL statements:
select * from sysobjects where xtype in ('P','TF','IF','FN') order by [Name]
All stored procedure and function names can be queried. The results of the loop query,
In each loop, the function or stored procedure name is stored in a string SpobjectName, and then the stored procedure is called: sp_helptext is as follows:
exec sp_helptext '" +SpobjectName+"'
Returns a table that contains the text content in the SpobjectName stored procedure. One row of text in the stored procedure corresponds to one row in the table.
Execute all SPs and functions with exec sp_helptext to get the codes of all stored procedures and functions.
Just put the results in a table and compare the similarities and differences.
Reference: SQL Server Books Online
<Automated generation of stored procedures in database>( http://www.vckbase.com/document/viewdoc/?id=1111 )
Now we only compare column attributes of tables and views, as well as stored procedures and functions.
The constraint relationship between comparison tables is not implemented.
Will be added later.