MS
-SQL数据库开发常用汇总 1.按姓氏笔画排序:
Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as
2.数据库加密:
select encrypt('原始密码')
select pwdencrypt('原始密码')
select pwdcompare('原始密码','加密后密码') = 1--相同；否则不相同 encrypt('原始密码')
select pwdencrypt('原始密码')
select pwdcompare('原始密码','加密后密码') = 1--相同；否则不相同
3.取回表中字段:
declare @list varchar(1000),@sql nvarchar(1000)
select @list=@list+','+b.name from sysobjects a,syscolumns b where a.id=b.id and a.name='表A'
set @sql='select '+right(@list,len(@list)-1)+' from 表A'
exec (@sql)
4.查看硬盘分区:
EXEC master..xp_fixeddrives
5.比较A,B表是否相等:
if (select checksum_agg(binary_checksum(*)) from A)
=
(select checksum_agg(binary_checksum(*)) from B)
print '相等'
else
print '不相等'
6.杀掉所有的事件探察器进程:
DECLARE hcforeach CURSOR GLOBAL FOR SELECT 'kill '+RTRIM(spid) FROM master.dbo.sysprocesses
WHERE program_name IN('SQL profiler',N'SQL 事件探查器')
EXEC sp_msforeach_worker '?'
7.记录搜索:
开头到N条记录
Select Top N * From 表
-------------------------------
N到M条记录(要有主索引ID)
Select Top M-N * From 表 Where ID in (Select Top M ID From 表) Order by ID Desc
----------------------------------
N到结尾记录
Select Top N * From 表 Order by ID Desc
8.如何修改数据库的名称:
sp_renamedb 'old_name', 'new_name'
9：获取当前数据库中的所有用户表
select Name from sysobjects where xtype='u' and status>=0
10：获取某一个表的所有字段
select name from syscolumns where id=object_id('表名')
11：查看与某一个表相关的视图、存储过程、函数
select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%表名%'
12：查看当前数据库中所有存储过程
select name as 存储过程名称 from sysobjects where xtype='P'
13：查询用户创建的所有数据库
select * from master..sysdatabases D where sid not in(select sid from master..syslogins where name='sa')
或者
select dbid, name AS DB_NAME from master..sysdatabases where sid ＜> 0x01
14：查询某一个表的字段和数据类型
select column_name,data_type from information_schema.columns
where table_name = '表名'
[n].[标题]:
Select * From TableName Order By CustomerName
[n].[标题]:
来自
http://dev.csdn.net/develop/article/83/83138.shtm
一、 只复制一个表结构，不复制数据
select top 0 * into [t1] from [t2]
二、 获取数据库中某个对象的创建脚本
1、 先用下面的脚本创建一个函数
if exists(select 1 from sysobjects where id=object_id('fgetscript') and objectproperty(id,'IsInlineFunction')=0)
drop function fgetscript
go
create function fgetscript(
@servername varchar(50) --服务器名
,@userid varchar(50)='sa' --用户名,如果为nt验证方式,则为空
,@password varchar(50)='' --密码
,@databasename varchar(50) --数据库名称
,@objectname varchar(250) --对象名
) returns varchar(8000)
as
begin
declare @re varchar(8000) --返回脚本
declare @srvid int,@dbsid int --定义服务器、数据库集id
declare @dbid int,@tbid int --数据库、表id
declare @err int,@src varchar(255), @desc varchar(255) --错误处理变量
--创建sqldmo对象
exec @err=sp_oacreate 'sqldmo.sqlserver',@srvid output
if @err<>0 goto lberr
--连接服务器
if isnull(@userid,'')='' --如果是 Nt验证方式
begin
exec @err=sp_oasetproperty @srvid,'loginsecure',1
if @err<>0 goto lberr
exec @err=sp_oamethod @srvid,'connect',null,@servername
end
else
exec @err=sp_oamethod @srvid,'connect',null,@servername,@userid,@password
if @err<>0 goto lberr
--获取数据库集
exec @err=sp_oagetproperty @srvid,'databases',@dbsid output
if @err<>0 goto lberr
--获取要取得脚本的数据库id
exec @err=sp_oamethod @dbsid,'item',@dbid output,@databasename
if @err<>0 goto lberr
--获取要取得脚本的对象id
exec @err=sp_oamethod @dbid,'getobjectbyname',@tbid output,@objectname
if @err<>0 goto lberr
--取得脚本
exec @err=sp_oamethod @tbid,'script',@re output
if @err<>0 goto lberr
--print @re
return(@re)
lberr:
exec sp_oageterrorinfo NULL, @src out, @desc out
declare @errb varbinary(4)
set @errb=cast(@err as varbinary(4))
exec master..xp_varbintohexstr @errb,@re out
set @re='错误号: '+@re
+char(13)+'错误源: '+@src
+char(13)+'错误描述: '+@desc
return(@re)
end
go
2、 用法如下
用法如下,
print dbo.fgetscript('服务器名','用户名','密码','数据库名','表名或其它对象名')
3、 如果要获取库里所有对象的脚本，如如下方式
declare @name varchar(250)
declare #aa cursor for
select name from sysobjects where xtype not in('S','PK','D','X','L')
open #aa
fetch next from #aa into @name
while @@fetch_status=0
begin
print dbo.fgetscript('onlytiancai','sa','sa','database',@name)
fetch next from #aa into @name
end
close #aa
deallocate #aa
4、 声明，此函数是csdn邹建邹老大提供的
三、 分隔字符串
如果有一个用逗号分割开的字符串，比如说"a,b,c,d,1,2,3,4"，如何用t-sql获取这个字符串有几个元素，获取第几个元素的值是多少呢？因为t-sql里没有split函数，也没有数组的概念，所以只能自己写几个函数了。
1、 获取元素个数的函数
create function getstrarrlength (@str varchar(8000))
returns int
as
begin
declare @int_return int
declare @start int
declare @next int
declare @location int
select @str =','+ @str +','
select @str=replace(@str,',,',',')
select @start =1
select @next =1
select @location = charindex(',',@str,@start)
while (@location <>0)
begin
select @start = @location +1
select @location = charindex(',',@str,@start)
select @next =@next +1
end
select @int_return = @next-2
return @int_return
end
2、 获取指定索引的值的函数
create function getstrofindex (@str varchar(8000),@index int =0)
returns varchar(8000)
as
begin
declare @str_return varchar(8000)
declare @start int
declare @next int
declare @location int
select @start =1
select @next =1 --如果习惯从0开始则select @next =0
select @location = charindex(',',@str,@start)
while (@location <>0 and @index > @next )
begin
select @start = @location +1
select @location = charindex(',',@str,@start)
select @next =@next +1
end
if @location =0 select @location =len(@str)+1 --如果是因为没有逗号退出，则认为逗号在字符串后
select @str_return = substring(@str,@start,@location -@start) --@start肯定是逗号之后的位置或者就是初始值1
if (@index <> @next ) select @str_return = '' --如果二者不相等，则是因为逗号太少，或者@index小于@next的初始值1。
return @str_return
end
3、 测试
SELECT [dbo].[getstrarrlength]('1,2,3,4,a,b,c,d')
SELECT [dbo].[getstrofindex]('1,2,3,4,a,b,c,d',5)
四、 一条语句执行跨越若干个数据库
我要在一条语句里操作不同的服务器上的不同的数据库里的不同的表,怎么办呢?
第一种方法:
select * from OPENDATASOURCE('SQLOLEDB','Data Source=远程ip;User ID=sa;Password=密码').库名.dbo.表名
第二种方法:
先使用联结服务器:
EXEC sp_addlinkedserver '别名','','MSDASQL',NULL,NULL,'DRIVER={SQL Server};SERVER=远程名;UID=用户;PWD=密码;'
exec sp_addlinkedsrvlogin @rmtsrvname='别名',@useself='false',@locallogin='sa',@rmtuser='sa',@rmtpassword='密码'
GO
然后你就可以如下：
select * from 别名.库名.dbo.表名
insert 库名.dbo.表名 select * from 别名.库名.dbo.表名
select * into 库名.dbo.新表名 from 别名.库名.dbo.表名
go
五、 怎样获取一个表中所有的字段信息
蛙蛙推荐：怎样获取一个表中所有字段的信息
先创建一个视图
Create view fielddesc
as
select o.name as table_name,c.name as field_name,t.name as type,c.length as
length,c.isnullable as isnullable,convert(varchar(30),p.value) as desp
from syscolumns c
join systypes t on c.xtype = t.xusertype
join sysobjects o on o.id=c.id
left join sysproperties p on p.smallid=c.colid and p.id=o.id
where o.xtype='U'
查询时:
Select * from fielddesc where table_name = '你的表名'
还有个更强的语句，是邹建写的，也写出来吧
SELECT
(case when a.colorder=1 then d.name else '' end) N'表名',
a.colorder N'字段序号',
a.name N'字段名',
(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) N'标识',
(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) N'主键',
b.name N'类型',
a.length N'占用字节数',
COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'长度',
isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'小数位数',
(case when a.isnullable=1 then '√'else '' end) N'允许空',
isnull(e.text,'') N'默认值',
isnull(g.[value],'') AS N'字段说明'
--into ##tx
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 sysproperties g
on a.id=g.id AND a.colid = g.smallid
order by object_name(a.id),a.colorder
六、 时间格式转换问题
因为新开发的软件需要用一些旧软件生成的一些数据，在时间格式上不统一，只能手工转换，研究了一下午写了三条语句，以前没怎么用过convert函数和case语句，还有"+"操作符在不同上下文环境也会起到不同的作用，把我搞晕了要，不过现在看来是差不多弄好了。
1、把所有"70.07.06"这样的值变成"1970-07-06"
UPDATE lvshi
SET shengri = '19' + REPLACE(shengri, '.', '-')
WHERE (zhiyezheng = '139770070153')
2、在"1970-07-06"里提取"70","07","06"
SELECT SUBSTRING(shengri, 3, 2) AS year, SUBSTRING(shengri, 6, 2) AS month,
SUBSTRING(shengri, 9, 2) AS day
FROM lvshi
WHERE (zhiyezheng = '139770070153')
3、把一个时间类型字段转换成"1970-07-06"
UPDATE lvshi
SET shenling = CONVERT(varchar(4), YEAR(shenling))
+ '-' + CASE WHEN LEN(MONTH(shenling)) = 1 THEN '0' + CONVERT(varchar(2),
month(shenling)) ELSE CONVERT(varchar(2), month(shenling))
END + '-' + CASE WHEN LEN(day(shenling)) = 1 THEN '0' + CONVERT(char(2),
day(shenling)) ELSE CONVERT(varchar(2), day(shenling)) END
WHERE (zhiyezheng = '139770070153')
七、 分区视图
分区视图是提高查询性能的一个很好的办法
--看下面的示例
--示例表
create table tempdb.dbo.t_10(
id int