📄 hotfix.sql
字号:
if @table_owner is null
begin /* If unqualified table name */
SELECT @full_table_name = @table_name
end
else
begin /* Qualified table name */
SELECT @full_table_name = @table_owner + '.' + @table_name
end
/* Get Object ID */
SELECT @table_id = object_id(@full_table_name)
if ((charindex('%',@full_table_name) = 0) and
(charindex('_',@full_table_name) = 0) and
@table_id <> 0)
begin
/* this block is for the case where there is no pattern
matching required for the table name */
SELECT
TABLE_QUALIFIER = convert(varchar(32),DB_NAME()),
TABLE_OWNER = convert(varchar(32),USER_NAME(o.uid)),
TABLE_NAME = convert(varchar(32),o.name),
COLUMN_NAME = convert(varchar(32),c.name),
d.DATA_TYPE,
TYPE_NAME = t.name,
"PRECISION" = isnull(d.data_precision, convert(int,c.length)),
LENGTH = isnull(d.length, convert(int,c.length)),
SCALE = d.numeric_scale,
d.RADIX,
NULLABLE = /* set nullability from status flag */
convert(smallint, convert(bit, c.status&8)),
REMARKS = convert(varchar(254),null), /* Remarks are NULL */
COLUMN_DEF = text,
d.SQL_DATA_TYPE,
d.SQL_DATETIME_SUB,
CHAR_OCTET_LENGTH = isnull(d.data_precision, convert(int,c.length))+d.charbin,
ORDINAL_POSITION = convert(int,
(
select count(*)
from syscolumns sc
where sc.id = c.id
AND sc.number = c.number
AND sc.colid <= c.colid
)),
IS_NULLABLE = convert(varchar(254),rtrim(substring('NO YES',(c.status&8)+1,3))),
SS_DATA_TYPE = c.type
FROM
syscolumns c,
sysobjects o,
syscomments m,
master.dbo.spt_datatype_info d,
systypes t
WHERE
o.id = @table_id
AND c.id = o.id
AND t.type = d.ss_dtype
AND c.length = isnull(d.fixlen, c.length)
AND (d.ODBCVer is null or d.ODBCVer = @ODBCVer)
AND o.type <> 'P'
AND c.usertype = t.usertype
AND c.name like @column_name
AND c.cdefault *= m.id
AND m.colid = 1
ORDER BY 17
end
else
begin
/* this block is for the case where there IS pattern
matching done on the table name */
if @table_owner is null /* If owner not supplied, match all */
select @table_owner = '%'
SELECT
TABLE_QUALIFIER = convert(varchar(32),DB_NAME()),
TABLE_OWNER = convert(varchar(32),USER_NAME(o.uid)),
TABLE_NAME = convert(varchar(32),o.name),
COLUMN_NAME = convert(varchar(32),c.name),
d.DATA_TYPE,
TYPE_NAME = t.name,
"PRECISION" = isnull(d.data_precision, convert(int,c.length)),
LENGTH = isnull(d.length, convert(int,c.length)),
SCALE = d.numeric_scale,
d.RADIX,
NULLABLE = /* set nullability from status flag */
convert(smallint, convert(bit, c.status&8)),
REMARKS = convert(varchar(254),null), /* Remarks are NULL */
COLUMN_DEF = text,
d.SQL_DATA_TYPE,
d.SQL_DATETIME_SUB,
CHAR_OCTET_LENGTH = isnull(d.data_precision, convert(int,c.length))+d.charbin,
ORDINAL_POSITION = convert(int,
(
select count(*)
from syscolumns sc
where sc.id = c.id
AND sc.number = c.number
AND sc.colid <= c.colid
)),
IS_NULLABLE = convert(varchar(254),rtrim(substring('NO YES',(c.status&8)+1,3))),
SS_DATA_TYPE = c.type
FROM
syscolumns c,
sysobjects o,
syscomments m,
master.dbo.spt_datatype_info d,
systypes t
WHERE
o.name like @table_name
AND user_name(o.uid) like @table_owner
AND o.id = c.id
AND t.type = d.ss_dtype
AND c.length = isnull(d.fixlen, c.length)
AND (d.ODBCVer is null or d.ODBCVer = @ODBCVer)
AND o.type <> 'P'
AND c.usertype = t.usertype
AND c.name like @column_name
AND c.cdefault *= m.id
AND m.colid = 1
ORDER BY 2, 3, 17
end
go
if (charindex('6.00', @@version) = 0 and
charindex('6.50', @@version) = 0 and
charindex('7.00', @@version) = 0 and
charindex('8.00', @@version) = 0)
begin
print ''
print ''
print 'Warning:'
print 'you are installing the stored procedures '
print 'on a pre 6.0 SQL Server.'
print 'Ignore the following error.'
end
else
drop proc sp_columns
go
/* Procedure for 6.0 and 6.50 server */
CREATE PROCEDURE sp_columns (
@table_name varchar(96),
@table_owner varchar(96) = null,
@table_qualifier varchar(32) = null,
@column_name varchar(96) = null,
@ODBCVer int = 2)
AS
DECLARE @full_table_name varchar(193)
DECLARE @table_id int
if @ODBCVer <> 3
select @ODBCVer = 2
if @column_name is null /* If column name not supplied, match all */
select @column_name = '%'
if @table_qualifier is not null
begin
if db_name() <> @table_qualifier
begin /* If qualifier doesn't match current database */
raiserror (15250, -1,-1)
return
end
end
if @table_name is null
begin
/* If table name not supplied, match all */
select @table_name = '%'
end
if @table_owner is null
begin
/* If unqualified table name */
SELECT @full_table_name = @table_name
end
else
begin
/* Qualified table name */
SELECT @full_table_name = @table_owner + '.' + @table_name
end
/* Get Object ID */
SELECT @table_id = object_id(@full_table_name)
if ((charindex('%',@full_table_name) = 0) and
(charindex('[',@full_table_name) = 0) and
(charindex('_',@full_table_name) = 0) and
@table_id <> 0)
begin
/* this block is for the case where there is no pattern
matching required for the table name */
SELECT
TABLE_QUALIFIER = convert(varchar(32),DB_NAME()),
TABLE_OWNER = convert(varchar(32),USER_NAME(o.uid)),
TABLE_NAME = convert(varchar(32),o.name),
COLUMN_NAME = convert(varchar(32),c.name),
DATA_TYPE = d.DATA_TYPE,
TYPE_NAME = convert(varchar(32),
case
when t.usertype > 100 or t.usertype in (18,80) then t.name
else d.TYPE_NAME
end),
"PRECISION" = convert(int,
case
when d.DATA_TYPE in (6,7) then d.data_precision /* FLOAT/REAL */
else isnull(convert(int,c.prec), 2147483647)
end),
LENGTH = convert(int,
case
when d.ss_dtype IN (106, 108, 55, 63) then /* decimal/numeric types */
c.prec+2
else
isnull(d.length, c.length)
end),
SCALE = convert(smallint, c.scale),
RADIX = d.RADIX,
NULLABLE = /* set nullability from status flag */
convert(smallint, convert(bit, c.status&8)),
REMARKS = convert(varchar(254),null), /* Remarks are NULL */
COLUMN_DEF = text,
SQL_DATA_TYPE = d.SQL_DATA_TYPE,
SQL_DATETIME_SUB = d.SQL_DATETIME_SUB,
CHAR_OCTET_LENGTH = isnull(convert(int,c.prec), 2147483647)+d.charbin,
ORDINAL_POSITION = convert(int,
(
select count(*)
from syscolumns sc
where sc.id = c.id
AND sc.number = c.number
AND sc.colid <= c.colid
)),
IS_NULLABLE = convert(varchar(254),rtrim(substring('NO YES',(c.status&8)+1,3))),
SS_DATA_TYPE = c.type
FROM
syscolumns c,
sysobjects o,
syscomments m,
master.dbo.spt_datatype_info d,
systypes t
WHERE
o.id = @table_id
AND c.id = o.id
AND t.type = d.ss_dtype
AND c.length = isnull(d.fixlen, c.length)
AND (d.ODBCVer is null or d.ODBCVer = @ODBCVer)
AND o.type <> 'P'
AND isnull(d.AUTO_INCREMENT,0) = (c.status&128)/128
AND c.usertype = t.usertype
AND c.name like @column_name
AND c.cdefault *= m.id
AND m.colid = 1
ORDER BY 17
end
else
begin
/* this block is for the case where there IS pattern
matching done on the table name */
if @table_owner is null /* If owner not supplied, match all */
select @table_owner = '%'
SELECT
TABLE_QUALIFIER = convert(varchar(32),DB_NAME()),
TABLE_OWNER = convert(varchar(32),USER_NAME(o.uid)),
TABLE_NAME = convert(varchar(32),o.name),
COLUMN_NAME = convert(varchar(32),c.name),
DATA_TYPE = d.DATA_TYPE,
TYPE_NAME = convert(varchar(32),
case
when t.usertype > 100 or t.usertype in (18,80) then t.name
else d.TYPE_NAME
end),
"PRECISION" = convert(int,
case
when d.DATA_TYPE in (6,7) then d.data_precision /* FLOAT/REAL */
else isnull(convert(int,c.prec), 2147483647)
end),
LENGTH = convert(int,
case
when d.ss_dtype IN (106, 108, 55, 63) then /* decimal/numeric types */
c.prec+2
else
isnull(d.length, c.length)
end),
SCALE = convert(smallint, c.scale),
RADIX = d.RADIX,
NULLABLE = /* set nullability from status flag */
convert(smallint, convert(bit, c.status&8)),
REMARKS = convert(varchar(254),null), /* Remarks are NULL */
COLUMN_DEF = text,
SQL_DATA_TYPE = d.SQL_DATA_TYPE,
SQL_DATETIME_SUB = d.SQL_DATETIME_SUB,
CHAR_OCTET_LENGTH = isnull(convert(int,c.prec), 2147483647) + d.charbin,
ORDINAL_POSITION = convert(int,
(
select count(*)
from syscolumns sc
where sc.id = c.id
AND sc.number = c.number
AND sc.colid <= c.colid
)),
IS_NULLABLE = convert(varchar(254),rtrim(substring('NO YES',(c.status&8)+1,3))),
SS_DATA_TYPE = c.type
FROM
syscolumns c,
sysobjects o,
syscomments m,
master.dbo.spt_datatype_info d,
systypes t
WHERE
o.name like @table_name
AND user_name(o.uid) like @table_owner
AND o.id = c.id
AND t.type = d.ss_dtype
AND c.length = isnull(d.fixlen, c.length)
AND (d.ODBCVer is null or d.ODBCVer = @ODBCVer)
AND o.type <> 'P'
AND isnull(d.AUTO_INCREMENT,0) = (c.status&128)/128
AND c.usertype = t.usertype
AND c.name like @column_name
AND c.cdefault *= m.id
AND m.colid = 1
ORDER BY 2, 3, 17
end
go
if (charindex('7.00', @@version) = 0 and
charindex('8.00', @@version) = 0)
begin
print ''
print ''
print 'Warning:'
print 'you are installing the stored procedures '
print 'on a pre 7.0 SQL Server.'
print 'Ignore the following errors.'
end
else
drop proc sp_columns
go
/* Procedure for 7.0 server */
CREATE PROCEDURE sp_columns (
@table_name nvarchar(384),
@table_owner nvarchar(384) = null,
@table_qualifier sysname = null,
@column_name nvarchar(384) = null,
@ODBCVer int = 2)
AS
DECLARE @full_table_name nvarchar(769)
DECLARE @table_id int
if @ODBCVer <> 3
select @ODBCVer = 2
if @column_name is null /* If column name not supplied, match all */
select @column_name = '%'
if @table_qualifier is not null
begin
if db_name() <> @table_qualifier
begin /* If qualifier doesn't match current database */
raiserror (15250, -1,-1)
return
end
end
if @table_name is null
begin /* If table name not supplied, match all */
select @table_name = '%'
end
if @table_owner is null
begin /* If unqualified table name */
SELECT @full_table_name = quotename(@table_name)
end
else
begin /* Qualified table name */
if @table_owner = ''
begin /* If empty owner name */
SELECT @full_table_name = quotename(@table_owner)
end
else
begin
SELECT @full_table_name = quotename(@table_owner) +
'.' + quotename(@table_name)
end
end
/* Get Object ID */
SELECT @table_id = object_id(@full_table_name)
if ((isnull(charindex('%', @full_table_name),0) = 0) and
(isnull(charindex('[', @table_name),0) = 0) and
(isnull(charindex('[', @table_owner),0) = 0) and
(isnull(charindex('_', @full_table_name),0) = 0) and
@table_id <> 0)
begin
/* this block is for the case where there is no pattern
matching required for the table name */
SELECT
TABLE_QUALIFIER = convert(sysname,DB_NAME()),
TABLE_OWNER = convert(sysname,USER_NAME(o.uid)),
TABLE_NAME = convert(sysname,o.name),
COLUMN_NAME = convert(sysname,c.name),
d.DATA_TYPE,
convert (sysname,case
when t.xusertype > 255 then t.name
else d.TYPE_NAME
end) TYPE_NAME,
convert(int,case
when d.DATA_TYPE in (6,7) then d.data_precision /* FLOAT/REAL */
else OdbcPrec(c.xtype,c.length,c.xprec)
end) "PRECISION",
convert(int,case
when type_name(d.ss_dtype) IN ('numeric','decimal') then /* decimal/numeric types */
OdbcPrec(c.xtype,c.length,c.xprec)+2
else
isnull(d.length, c.length)
end) LENGTH,
SCALE = convert(smallint, OdbcScale(c.xtype,c.xscale)),
d.RADIX,
NULLABLE = convert(smallint, ColumnProperty (c.id, c.name, 'AllowsNull')),
REMARKS = convert(varchar(254),null), /* Remarks are NULL */
COLUMN_DEF = text,
d.SQL_DATA_TYPE,
d.SQL_DATETIME_SUB,
CHAR_OCTET_LENGTH = isnull(d.length, c.length)+d.charbin,
ORDINAL_POSITION = convert(int,
(
select count(*)
from syscolumns sc
where sc.id = c.id
AND sc.number = c.number
AND sc.colid <= c.colid
)),
IS_NULLABLE = convert(varchar(254),
substring('NO YES',(ColumnProperty (c.id, c.name, 'AllowsNull')*3)+1,3)),
SS_DATA_TYPE = c.type
FROM
sysobjects o,
master.dbo.spt_datatype_info d,
systypes t,
syscolumns c
LEFT OUTER JOIN syscomments m on c.cdefault = m.id
AND m.colid = 1
WHERE
o.id = @table_id
AND c.id = o.id
AND t.xtype = d.ss_dtype
AND c.length = isnull(d.fixlen, c.length)
AND (d.ODBCVer is null or d.ODBCVer = @ODBCVer)
AND (o.type not in ('P', 'FN', 'TF', 'IF') OR (o.type in ('TF', 'IF') and c.number = 0))
AND isnull(d.AUTO_INCREMENT,0) = isnull(Colum⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -