1.跨服务器连接数据表
--打开服务器配置'Ad Hoc Distributed Queries'
--exec sp_configure 'show advanced options',1
--reconfigure with override
--exec sp_configure 'Ad Hoc Distributed Queries',1
--reconfigure with override
SELECT *
FROM OPENDATASOURCE('sqloledb','data source=172.168.1.52; user id=sa; password=888').[ce_learning].[dbo].[T_LEARNING_PROJECT] a
left join [ce_learning].[dbo].[T_LEARNING_PROJECT_TERM]b on a.PROJECTID=b.Projectid
2.临时表判断
IF OBJECT_ID(N'tempdb.dbo.#temp1') IS NOT NULL
BEGIN
DROP TABLE #temp1
END
3. openrowset excel:SQL Server 打开Excel
select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:\测试.xlsx', 'select * from [Sheet1$]')
4.去空格
ltrim(rtrim(RET.姓名))
5.数据库创建
create database HNWSupDB
on
(name = 'HNWSupDB', filename ='d:\database\HNWSupDB.mdf',
size = 200MB,
maxsize = unlimited
)
LOG ON
(NAME = 'HNWSupDB_log', filename = 'd:\database\HNWSupDB.ldf',
size = 100mb,
maxsize = unlimited
)
6.备份数据库
backup database [dbname] to disk='D:\DATAUP\dbname.bak' with format
restore database MasterManageDB_test from disk = 'D:\DataBackup\MasterManageDB_TEST.bak'
with replace
--复制数据库
RESTORE DATABASE [ProjectManageDB_test] FROM DISK = N'D:\DataBackup\ProjectManageDB_20151203.bak' with replace,
MOVE 'ProjectManage' TO 'D:\sqlDATA\ProjectManage_test.mdf',
MOVE 'ProjectManage_log' TO 'D:\sqlDATA\ProjectManage_test_log.ldf'
8.解密
SELECT * INTO Teacherinfo_tmp FROM
(
SELECT
id ,
[dbo].DecryptDES(a.Name,'8888') AS Name ,
[dbo].DecryptDES(a.IdNumber,'8888') AS IdNumber
FROM TeacherInfo a
) m
9.季度、月、周
DECLARE @Week INT ,@Month INT ,@Quarter INT
SELECT @Quarter=datepart(quarter,getdate())
SELECT @Month=MONTH(GETDATE())
SELECT @Week= datepart(week,getdate())
SELECT @Quarter, @Month ,@Week
10.自动添加0
SELECT left(ltrim(1)+replicate('0',12),12)
11.1赋值为0 ，0赋值为1
a.IsDel = (a.IsDel +1)%2
12.缩小日志文件
查看数据库的recovery_model_desc类型
SELECT NAME, recovery_model_desc FROM sys.databases
如果是FULL类型，修改为SIMPLE类型
ALTER DATABASE ASFCORE SET Recovery simple
收缩日志文件大小(单位是M)
DBCC SHRINKFILE (N'ASFCORE_log' , 10)
检查日志文件名称
USE ASFCORE
SELECT NAME, size FROM sys.database_files
恢复成FULL类型
ALTER DATABASE ASFCORE SET Recovery FULL
13.暂时关闭触发器DISABLE TRIGGER
ALTER TABLE dbo.AuditResult DISABLE TRIGGER ALL
--这里是操作
ALTER TABLE dbo.AuditResult ENABLE TRIGGER ALL
14.SQL Server数据库复制出现无法找到该行错误：查看执行的命令(删除或修改)
exec distribution..sp_browsereplcmds '0x0000002600077156000C00000000','0x0000002600077156000C00000000'
15.关于sql server 代理（已禁用代理xp）
sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'Agent XPs', 1;
GO
RECONFIGURE WITH OVERRIDE
GO
16.
还原或删除sql server 2008数据库时，出现：“因为数据库正在使用，所以无法获得对数据库的独占访问权”，
终解决方案如下，关键SQL语句：
ALTER DATABASE [datebase] SET OFFLINE WITH ROLLBACK IMMEDIATE
用完之后再
ALTER database [ datebase] set online
17.没有执行权限，赋予数据库文件权限
USE ProjectManageDB
GO
EXEC dbo.sp_changedbowner @loginame = N'sa', @map = false
GO
18.批量删除存储过程
declare proccur cursor
for
select [name] from sysobjects where type='P'
declare @procname varchar(100)
open proccur
fetch next from proccur into @procname
while(@@FETCH_STATUS = 0)
begin
exec('drop proc ' + @procname)
print(@procname + '已被删除')
fetch next from proccur into @procname
end
close proccur
deallocate proccur
--查看函数 存储过程 视图 具体代码
USE AdventureWorks;
GO
SELECT sm.object_id, OBJECT_NAME(sm.object_id) AS object_name, o.type, o.type_desc, sm.definition
FROM sys.sql_modules AS sm
JOIN sys.objects AS o ON sm.object_id = o.object_id
where o.type LIKE '%F%' and o.is_ms_shipped=0 --这里显示的是所有自定义函数
ORDER BY o.type;
GO
--复制标识列问题
DBCC CHECKIDENT([User], RESEED, 691106)
