SQL SERVER 中的dbcc inputbuffer

最新推荐文章于 2021-02-06 00:34:19 发布

yiyioo

最新推荐文章于 2021-02-06 00:34:19 发布

阅读量1w

点赞数

分类专栏： Sql Server 2000 文章标签： sql server permissions null parameters sql insert

本文链接：https://blog.csdn.net/yiyioo/article/details/649838

版权

Sql Server 2000 专栏收录该内容

0 篇文章 0 订阅

订阅专栏

想用一个触发器来记录用户对表的更新情况

在csdn上吭到了一个利用dbcc inputbuffer来记录当前使用的sql语句

可是没想到,记录的sql只有256个字符

于是查看sql帮助

看到了如下内容

DBCC INPUTBUFFER

Displays the last statement sent from a client to Microsoft® SQL Server™.

Syntax

DBCC INPUTBUFFER (spid)

Arguments

spid

Is the system process ID (SPID) for the user connection as displayed in the output of the sp_who system stored procedure.

Result Sets

DBCC INPUTBUFFER returns a rowset with the following columns.

Column name	Data type	Description
EventType	nvarchar(30)	Event type, for example: RPC, Language, or No Event.
Parameters	Int	0 = text 1- n = parameters
EventInfo	nvarchar(255)	For an EventType of RPC, EventInfo contains only the procedure name. For an EventType of Language or No Event, only the first 255 characters of the event are displayed.

For example, DBCC INPUTBUFFER returns the following result set when the last event in the buffer is DBCC INPUTBUFFER(11).

EventType      Parameters EventInfo             
-------------- ---------- --------------------- 
Language Event 0          DBCC INPUTBUFFER (11)

(1 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Note There are very brief transitional periods between events when no event can be displayed on Windows NT®. On Windows 98, an event is displayed only when active.

Permissions

DBCC INPUTBUFFER permissions default to members of the sysadmin fixed server role only, who can see any SPID. Other users can see any SPID they own. Permissions are not transferable.

Examples

This example assumes a valid SPID of 10.

DBCC INPUTBUFFER (10)

看到了最郁闷的东西

EventInfo 这个返回值,长度只有255个字符

不知道这个东西能不能override,要是能的话,那还不错,要不然就得自己写sql了,真是麻烦~``````

查了MSDN说不支持用户直接修改系统的表和存储过程等,又没说间接怎么改?
现在该上哪儿查呢~?

上csdn问了一下，有人给了个sql，让我改成存储过程，根据表名获得insert语句，

改成存储过程后如下：

drop Procedure tr_insert_emp
go
Create Procedure tr_insert_emp
@tablename varchar(100),
@out_sql nvarchar(4000) output
as
declare @out_sqlvalues nvarchar(4000)
set @out_sql ='('
set @out_sqlvalues = 'values(''+'
select @out_sqlvalues = @out_sqlvalues + cols + ' + '','' + ' ,@out_sql = @out_sql + '' + name + ','

from

(select case

when xtype in (48,52,56,59,60,62,104,106,108,122,127)

then 'case when '+ name +' is null then ''NULL'' else ' + 'cast('+ name + ' as varchar)'+' end'

when xtype in (58,61)

then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'cast('+ name +' as varchar)'+ '+'''''''''+' end'

when xtype in (167)

then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+'''''''''+' end'

when xtype in (231)

then 'case when '+ name +' is null then ''NULL'' else '+'''N'''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+'''''''''+' end'

when xtype in (175)

then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'cast(replace('+ name+','''''''','''''''''''') as Char(' + cast(length as varchar) + '))+'''''''''+' end'

when xtype in (239)

then 'case when '+ name +' is null then ''NULL'' else '+'''N'''''' + ' + 'cast(replace('+ name+','''''''','''''''''''') as Char(' + cast(length as varchar) + '))+'''''''''+' end'

else '''NULL'''

end as Cols,name

from syscolumns

where id = object_id(@tablename)

) T

select @out_sql ='select ''INSERT INTO '+ @tablename + ' ' + left(@out_sqlvalues,len(@out_sqlvalues)-4) + ')'' from '+@tablename

发现，这样update要写一个，delete也要写一个，so麻烦~~

有人说这样还不如在程序中处理，想想也是，程序中就有现成的sql语句。