1. 存储过程
CREATE PROCEDURE [dbo].[bbs_move_createtopic]
@fid smallint,
@iconid smallint,
@curtid INT OUTPUT
AS
BEGIN
INSERT INTO [topics] ......
SET @topicid=SCOPE_IDENTITY()
END
declare @curtid int
exec [createtopic] @fid=@new_fid,@iconid=0,
@curtid=@curr_topicid OUTPUT
2. 游标
DECLARE
@name NVARCHAR(100),
@id DECIMAL,
@idcard NVARCHAR(100)=NULL --声明游标
DECLARE cur_correctIdCard CURSOR
FOR
SELECT
ID, NAME, [USER_ID]
FROM dbo.old_bbstitle
WHERE (USER_ID IS NULL OR LTRIM(NAME)='')
OR (NAME IS NULL OR LTRIM(NAME)='')
--打开游标
OPEN cur_correctIdCard
--开始FETCH
FETCH NEXT FROM cur_correctIdCard INTO @id,@name,@idcard --0 FETCH 语句成功
--1 FETCH 语句失败或此行不在结果集中
--2 被提取的行不存在
WHILE(@@FETCH_STATUS=0)
BEGIN
... ... FETCH NEXT FROM cur_correctIdCard INTO @id,@name,@idcard
SET @idcard=NULL
END CLOSE cur_correctIdCard
DEALLOCATE cur_correctIdCard
3. 复制表的数据
SELECT * INTO users_bak FROM users
4. group by … having …
DELETE
FROM dbo.old_clubuser WHERE CLUBUSER_ID
IN(
SELECT MIN(CLUBUSER_ID) FROM dbo.old_clubuser WHERE CLUBUSER_NAME IS NOT NULL AND CLUBUSER_NICKNAME IS NOT NULL
GROUP BY CLUBUSER_NAME,CLUBUSER_NICKNAME
HAVING COUNT(CLUBUSER_NAME)>1
)
5. 事务
BEGIN TRANSACTION trans_correctIdCard
BEGIN TRY
DECLARE
@name NVARCHAR(100),
@id DECIMAL,
@idcard NVARCHAR(100)=NULL COMMIT TRANSACTION trans_correctIdCard
PRINT 'SUCCESS'
END TRY
BEGIN CATCH ROLLBACK TRANSACTION trans_correctIdCard PRINT '出错：'+CAST(@@error AS NVARCHAR(MAX))
PRINT '已进行回滚' END CATCH
5. 先判断表是否存在再创建表
--判断表是否存在，存在则先删除
IF object_id(N'dbo.T_Student',N'U') IS NOT NULL
DROP TABLE dbo.T_Student
GO --创建表
CREATE TABLE [dbo].[T_Student](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](max) NULL,
[Age] [int] NOT NULL,
[NickName] [nvarchar](max) NULL,
[City] [nvarchar](max) NULL,
CONSTRAINT [PK_dbo.T_Student] PRIMARY KEY CLUSTERED ([Id] ASC)
)
6. 获取SQL Server表结构
SELECT (case when a.colorder=1 then d.name else null end) 表名,
a.colorder 字段序号,a.name 字段名,
(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) 标识,
(case when (SELECT count(*) FROM sysobjects
WHERE (name in (SELECT name FROM sysindexes
WHERE (id = a.id) AND (indid in
(SELECT indid FROM sysindexkeys
WHERE (id = a.id) AND (colid in
(SELECT colid FROM syscolumns WHERE (id = a.id) AND (name = a.name)))))))
AND (xtype = 'PK'))>0 then '√' else '' end) 主键,b.name 类型,a.length 占用字节数,
COLUMNPROPERTY(a.id,a.name,'PRECISION') as 长度,
isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as 小数位数,(case when a.isnullable=1 then '√'else '' end) 允许空,
isnull(e.text,'') 默认值,isnull(g.[value], ' ') AS [说明]
FROM syscolumns a
left join systypes b on a.xtype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join syscomments e on a.cdefault=e.id
left join sys.extended_properties g on a.id=g.major_id AND a.colid=g.minor_id
left join sys.extended_properties f on d.id=f.class and f.minor_id=0
where b.name is not null
--WHERE d.name='要查询的表' --如果只查询指定表,加上此条件
order by a.id,a.colorder
（出处：http://www.cnblogs.com/ynbt/archive/2012/07/16/2593389.html）
7. 查询数据库中的所有数据库名
SELECT Name FROM Master..SysDatabases ORDER BY Name
8. 查询某个数据库中所有的表名
SELECT Name FROM SysObjects Where XType='U' ORDER BY Name
9. 批量清空一个库的表（1）
SELECT 'TRUNCATE TABLE ' + Name FROM SysObjects Where XType='U' ORDER BY Name
10. 批量清空一个库的表（2）
USE [库名]
DECLARE
@tableName NVARCHAR(100) --声明游标
DECLARE CUR_ALLTABLES CURSOR
FOR
SELECT Name FROM SysObjects Where XType='U' ORDER BY Name --打开游标
OPEN CUR_ALLTABLES
--开始FETCH
FETCH NEXT FROM CUR_ALLTABLES INTO @tableName DECLARE @SQL VARCHAR(MAX) = '' --0 FETCH 语句成功
--1 FETCH 语句失败或此行不在结果集中
--2 被提取的行不存在
WHILE(@@FETCH_STATUS=0)
BEGIN SET @SQL = 'TRUNCATE TABLE ' + @tableName
PRINT @SQL FETCH NEXT FROM CUR_ALLTABLES INTO @tableName
END CLOSE CUR_ALLTABLES
DEALLOCATE CUR_ALLTABLES
11. 行列转换的例子
WITH CTE AS (
select 1 as 序号 ,'2010-2-3' as 日期,'是' as 状
union all
select 2 as 序号 ,'2010-2-3' as 日期,'否' as 状
union all
select 3 as 序号 ,'2010-2-4' as 日期,'是' as 状
union all
select 4 as 序号 ,'2010-2-4' as 日期,'否' as 状
union all
select 5 as 序号 ,'2010-2-3' as 日期,'是' as 状
union all
select 6 as 序号 ,'2010-2-3' as 日期,'是' as 状
union all
select 7 as 序号 ,'2010-2-4' as 日期,'是' as 状
)
SELECT 日期, SUM(是) 是, SUM(否) 否 FROM (
SELECT 日期,
CASE WHEN 状='是' THEN 1 ELSE 0 END AS 是,
CASE WHEN 状='是' THEN 0 ELSE 1 END AS 否 FROM CTE
)A GROUP BY 日期
12. 获取一个存储过程的内容
DECLARE @Content NVARCHAR(max)
SELECT @Content=text
FROM syscomments
WHERE id = ( SELECT id FROM sysobjects WHERE name = 'StoredProcedureName')
PRINT @Content
13. 使用OFFSET/FETCH NEXT获取分页数据[SQL Server 2012]
注：OFFSET是从0开始的
DECLARE @Count int = 10
SELECT * FROM TableName(NOLOCK) WHERE Status&2=2
ORDER BY CreateTime DESC
OFFSET 0 ROW FETCH NEXT @Count rows only
14. top
DECLARE @COUNT INT = 10;
SELECT TOP(@COUNT) FROM T_Name
15. Create View
IF object_id(N'dbo.v_users',N'V') IS NOT NULL
DROP VIEW dbo.v_users
GO CREATE VIEW dbo.v_users
AS
SELECT *
FROM [dbo].[users]
16. 查看SP
sp_helptext spname
17. 获取执行统计信息
DBCC DROPCLEANBUFFERS 清除缓冲区
DBCC FREEPROCCACHE 删除计划高速缓存中的元素 SET STATISTICS TIME ON
SET STATISTICS IO ON SELECT 1 FROM Persons SET STATISTICS TIME OFF
SET STATISTICS IO OFF
相关： http://www.cnblogs.com/xqhppt/p/4041799.html
18. 修改字段
alter table Persons alter column Name nvarchar(12) not null
To Be Continued ...