1.查询数据库test的连接数
答：select * from sysprocesses where dbid in
(select dbid from sysdatabases where )
2.例如有张表tblname，假如某个时间对表tblname做了更新（添加、修改或删除），我如何知道什么时间它被更新过了呢？
答：再做一个表，管理所有的表的更新
用触发器，把修改记录放入spy表
spy表(eventType varchar(100),Parameters varchar(100),EventInfo varchar(100),modifydate datetime --默认getdate())
CREATE TRIGGER Trg_Spy ON tblname
FOR INSERT,DELETE,UPDATE
AS
DECLARE @tmpSQL NVARCHAR(300)
SET @tmpSQL= 'DBCC INPUTBUFFER('+CAST(@@SPID AS VARCHAR)+')'
INSERT INTO spy(eventType,Parameters,EventInfo)
EXEC (@tmpSQL)
3.如何判断数据库中有没有该表？
答：select * from dbo.sysobjects where [dbo].[表]') and OBJECTPROPERTY(id, N'IsUserTable') = 1
4.怎么写代码判断数据库中有该表，并且表中有无数据？
答：if exists(select * from dbo.sysobjects where [dbo].[表]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
if exists(select * from 表)
print '存在数据'
else
print '不存在数据'
else
print '不存在表'
5.请给我一些sql自定义函数参考代码。
答：
CREATE function F_GetGHYGroup(@GroupName nchar(30))
returns nvarchar(100)
as
begin
declare @str nvarchar(100)
select @str = (rtrim(ltrim(group_parent)) +'-'+ rtrim(ltrim(group_name))) from SYS_BPS_GROUPS where Group_Name = @GroupName and dbo.F_IsGHY_SZ(@GroupName) = 1 and dbo.F_IsGHY_Foot(@GroupName) = 1
return replace(@str,'某公司','要替换的名')
end
CREATE function F_IsGHY_SZ(@GroupName nchar(30))
returns int
as
begin
declare @str nchar(30),@bool int,@i int
set @i = 0
table_loop:
select @str = Group_Parent from SYS_BPS_GROUPS where Group_Name = @GroupName
if @@rowcount > 0 and rtrim(ltrim(@str)) <> '销售' or @i >1000
begin
if rtrim(ltrim(@str)) ='公司'
set @bool = 1
else
begin
set @GroupName = @str
set @i = @i + 1
goto table_loop
end
end
else
set @bool = -1
return @bool
end
CREATE function F_IsGHY_SZ(@GroupName nchar(30))
returns int
as
begin
declare @str nchar(30),@bool int,@i int
set @i = 0
table_loop:
select @str = Group_Parent from SYS_BPS_GROUPS where Group_Name = @GroupName
if @@rowcount > 0 and rtrim(ltrim(@str)) <> '公司1' and rtrim(ltrim(@str)) <> '公司2' and rtrim(ltrim(@str)) <> '公司3' and rtrim(ltrim(@str)) <> '公司4' and rtrim(ltrim(@str)) <> '公司5' and rtrim(ltrim(@str)) <> '公司6' and rtrim(ltrim(@str)) <> '部门1' and rtrim(ltrim(@str)) <> '部门2' and rtrim(ltrim(@str)) <> '销售' or @i >1000
begin
if rtrim(ltrim(@str)) ='集团'
set @bool = 1
else
begin
set @GroupName = @str
set @i = @i + 1
goto table_loop
end
end
else
set @bool = -1
return @bool
end
6.数据库备份相关代码
答：
1)本地备份：
declare @file varchar(30)
set @file='d:\dan'+convert(varchar(20),getdate(),112)+'.bak'
backup database epback to disk=@file with init
2)异地备份：
declare @sql varchar(500)
select @sql='\\127.0.0.1\test\ePBack'+convert(varchar(10),getdate(),112)+'.bak'
exec master..xp_cmdshell 'net use \\127.0.0.1\test 123456 /user:127.0.0.1\Administrator'
backup database epback to disk=@sql with init
exec master..xp_cmdshell 'net use \\127.0.0.1\test /delete'
评论