6、刪除臨時(shí)表:
drop table temp1
drop table temp2
================================
B:
create table a_dist(id int,name varchar(20))

insert into a_dist values(1,'abc')
insert into a_dist values(1,'abc')
insert into a_dist values(1,'abc')
insert into a_dist values(1,'abc')

exec up_distinct 'a_dist','id'

select * from a_dist

create procedure up_distinct(@t_name varchar(30),@f_key varchar(30))
--f_key表示是分組字段﹐即主鍵字段
as
begin
declare @max integer,@id varchar(30) ,@sql varchar(7999) ,@type integer
select @sql = 'declare cur_rows cursor for select '+@f_key+' ,count(*) from ' +@t_name +' 
group by ' +@f_key +' having count(*) > 1'
exec(@sql)
open cur_rows 
fetch cur_rows into @id,@max 
while @@fetch_status=0 
begin 
select @max = @max -1 
set rowcount @max 
select @type = xtype from syscolumns where id=object_id(@t_name) and name=@f_key
if @type=56
select @sql = 'delete from '+@t_name+' where ' + @f_key+' = '+ @id 
if @type=167
select @sql = 'delete from '+@t_name+' where ' + @f_key+' = '+''''+ @id +'''' 
exec(@sql)
fetch cur_rows into @id,@max 
end 
close cur_rows 
deallocate cur_rows
set rowcount 0
end

select * from systypes
select * from syscolumns where id = object_id('a_dist')


行列轉(zhuǎn)換--普通 

假設(shè)有張學(xué)生成績(jī)表(CJ)如下 
Name Subject Result 
張三 語(yǔ)文 80 
張三 數(shù)學(xué) 90 
張三 物理 85 
李四 語(yǔ)文 85 
李四 數(shù)學(xué) 92 
李四 物理 82 

想變成 
姓名 語(yǔ)文 數(shù)學(xué) 物理 
張三 80 90 85 
李四 85 92 82 

declare @sql varchar(4000) 
set @sql = 'select Name' 
select @sql = @sql + ',sum(case Subject when '''+Subject+''' then Result end) ['+Subject+']' 
from (select distinct Subject from CJ) as a 
select @sql = @sql+' from test group by name' 
exec(@sql) 

行列轉(zhuǎn)換--合并 

有表A, 
id pid 
1 1 
1 2 
1 3 
2 1 
2 2 
3 1 
如何化成表B: 
id pid 
1 1,2,3 
2 1,2 
3 1 

創(chuàng)建一個(gè)合并的函數(shù) 
create function fmerg(@id int) 
returns varchar(8000) 
as 
begin 
declare @str varchar(8000) 
set @str='' 
select @str=@str+','+cast(pid as varchar) from 表A where id=@id 
set @str=right(@str,len(@str)-1) 
return(@str) 
End 
go 

--調(diào)用自定義函數(shù)得到結(jié)果 
select distinct id,dbo.fmerg(id) from 表A 


如何取得一個(gè)數(shù)據(jù)表的所有列名 

方法如下：先從SYSTEMOBJECT系統(tǒng)表中取得數(shù)據(jù)表的SYSTEMID,然后再SYSCOLUMN表中取得該數(shù)據(jù)表的所有
列名。 
SQL語(yǔ)句如下： 
declare @objid int,@objname char(40) 
set @objname = 'tablename' 
select @objid = id from sysobjects where id = object_id(@objname) 
select 'Column_name' = name from syscolumns where id = @objid order by colid 

或

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME ='users'


通過(guò)SQL語(yǔ)句來(lái)更改用戶(hù)的密碼 

修改別人的,需要sysadmin role 
EXEC sp_password NULL, 'newpassword', 'User' 

如果帳號(hào)為SA執(zhí)行EXEC sp_password NULL, 'newpassword', sa


怎么判斷出一個(gè)表的哪些字段不允許為空？ 

select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where IS_NULLABLE='NO' and 
TABLE_NAME=tablename 


如何在數(shù)據(jù)庫(kù)里找到含有相同字段的表？ 
a. 查已知列名的情況 
SELECT b.name as TableName,a.name as columnname 
From syscolumns a INNER JOIN sysobjects b 
ON a.id=b.id 
AND b.type='U' 
AND a.name='你的字段名字' 


未知列名查所有在不同表出現(xiàn)過(guò)的列名 
Select o.name As tablename,s1.name As columnname 
From syscolumns s1, sysobjects o 
Where s1.id = o.id 
And o.type = 'U' 
And Exists ( 
Select 1 From syscolumns s2 
Where s1.name = s2.name 
And s1.id <> s2.id 
) 


查詢(xún)第xxx行數(shù)據(jù) 

假設(shè)id是主鍵： 
select * from (select top xxx * from yourtable) aa where not exists(select 1 from (select 
top xxx-1 * from yourtable) bb where aa.id=bb.id) 

如果使用游標(biāo)也是可以的 
fetch absolute [number] from [cursor_name] 
行數(shù)為絕對(duì)行數(shù) 


SQL Server日期計(jì)算 
a. 一個(gè)月的第一天 
SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0) 
b. 本周的星期一 
SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0) 
c. 一年的第一天 
SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) 
d. 季度的第一天 
SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0) 
e. 上個(gè)月的最后一天 
SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)) 
f. 去年的最后一天 
SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)) 
g. 本月的最后一天 
SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0)) 
h. 本月的第一個(gè)星期一 
select DATEADD(wk, DATEDIFF(wk,0, 
dateadd(dd,6-datepart(day,getdate()),getdate()) 
), 0) 
i. 本年的最后一天 
SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))。 


獲取表結(jié)構(gòu)[把 'sysobjects' 替換 成 'tablename' 即可] 

SELECT CASE IsNull(I.name, '') 
When '' Then '' 
Else '*' 
End as IsPK, 
Object_Name(A.id) as t_name, 
A.name as c_name, 
IsNull(SubString(M.text, 1, 254), '') as pbc_init, 
T.name as F_DataType, 
CASE IsNull(TYPEPROPERTY(T.name, 'Scale'), '') 
WHEN '' Then Cast(A.prec as varchar) 
ELSE Cast(A.prec as varchar) + ',' + Cast(A.scale as varchar) 
END as F_Scale, 
A.isnullable as F_isNullAble 
FROM Syscolumns as A 
JOIN Systypes as T 
ON (A.xType = T.xUserType AND A.Id = Object_id('sysobjects') ) 
LEFT JOIN ( SysIndexes as I 
JOIN Syscolumns as A1 
ON ( I.id = A1.id and A1.id = object_id('sysobjects') and (I.status & 0x800) = 0x800 AND 
A1.colid <= I.keycnt) ) 
ON ( A.id = I.id AND A.name = index_col('sysobjects', I.indid, A1.colid) ) 
LEFT JOIN SysComments as M 
ON ( M.id = A.cdefault and ObjectProperty(A.cdefault, 'IsConstraint') = 1 ) 
ORDER BY A.Colid ASC


提取數(shù)據(jù)庫(kù)內(nèi)所有表的字段詳細(xì)說(shuō)明的SQL語(yǔ)句 

SELECT 
(case when a.colorder=1 then d.name else '' end) N'表名', 
a.colorder N'字段序號(hào)', 
a.name N'字段名', 
(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' 
end) N'標(biāo)識(shí)', 
(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'類(lèi)型', 
a.length N'占用字節(jié)數(shù)', 
COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'長(zhǎng)度', 
isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'小數(shù)位數(shù)', 
(case when a.isnullable=1 then '√'else '' end) N'允許空', 
isnull(e.text,'') N'默認(rèn)值', 
isnull(g.[value],'') AS N'字段說(shuō)明' 
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


快速獲取表test的記錄總數(shù)[對(duì)大容量表非常有效] 

快速獲取表test的記錄總數(shù): 
select rows from sysindexes where id = object_id('test') and indid in (0,1)

update 2 set KHXH=(ID+1)\2 2行遞增編號(hào)
update [23] set id1 = 'No.'+right('00000000'+id,6) where id not like 'No%' //遞增
update [23] set id1= 'No.'+right('00000000'+replace(id1,'No.',''),6) //補(bǔ)位遞增
delete from [1] where (id%2)=1 
奇數(shù)


替換表名字段
update [1] set domurl = replace(domurl,'Upload/Imgswf/','Upload/Photo/') where domurl like 
'%Upload/Imgswf/%'


截位
SELECT LEFT(表名, 5) 

最新評(píng)論