SQL语句总结一(MS SQL Server)

1．  获得SQL Server的安装路径

方法一、

declare @path varchar(200)

exec master.dbo.xp_regread

     'HKEY_LOCAL_MACHINE',

     'SOFTWARE/Microsoft/MSSQLSERVER/setup',

     'SQLpath',@path output

set @path=@path+'/data/'

print @path

方法二、

declare @path varchar(200)

select @path=filename from master.dbo.sysfiles

set @path=ltrim(reverse(@path))

set @path=reverse(substring(@path,charindex('/',@path),len(@path)))

print @path

2．  获取一个表的所有字段

select name from syscolumns where id=object_id('表名')

3．  获取存储过程相关的表名

    select name from sysobjects where id in

                            (select distinct bb.depid

                                    from sysobjects join sysdepends bb

                                    on bb.id=sysobjects.id

                            where sysobjects.name='存储过程名')

4. 列出数据库中的表

 

--列出数据库中的表(在数据库master中列出数据库test_database中的表,加前缀test_database..)

exec test_database..sp_tables

 

--列出用户表

exec test_database..sp_tables @table_type ='''table'''

5. 在当前数据库中根据字段名找出含有该字段的表

SELECT NAME FROM SYSOBJECTS WHERE ID IN (SELECT ID FROM SYSCOLUMNS WHERE NAME='Device')

6. varchar类型的排序问题

DECLARE @T TABLE
(
A VARCHAR(10)
)
INSERT INTO @T
SELECT '1' UNION ALL
SELECT '4' UNION ALL
SELECT '10'

SELECT * FROM @T
SELECT * FROM @T ORDER BY A
SELECT * FROM @T ORDER BY CONVERT(INT,A)
SELECT * FROM @T ORDER BY CAST(A AS INT)
SELECT * FROM @T ORDER BY RIGHT('0000'+A,2)
--左边补足够的0，2根据字段实际长度调整

7. 获得一年中某个月的天数

Create Proc GetMonthDays
(
@date datetime,
@cnt int output
)
as
begin
declare @yearmonth varchar(100),
@yearcurrentmonth datetime
set @yearmonth = convert(varchar(7), dateadd(month,1,@date), 121)
--print @yearmonth
set @yearmonth = @yearmonth +'-01'
--print @yearmonth
set @yearcurrentmonth = dateADD(day,-1,cast(@yearmonth as datetime))
--print @yearcurrentmonth
set @cnt = day(@yearcurrentmonth)
end

declare @cntt int
exec GetMonthDays '2006-12-01',@cntt output
print @cntt

8. 判断临时表是否存在

if object_id('Tempdb.dbo.#T') is not null

9. 删除前5天的记录

create table t(date1 datetime)
insert t select '2006-7-3 9:50:32'
union all select '2006-7-4 9:50:32'
union all select '2006-7-5 9:10:32'
union all select '2006-7-5 9:50:32'
union all select '2006-7-6 9:50:32'
union all select '2006-7-7 9:50:32'
union all select '2006-7-8 9:50:32'
union all select '2006-7-9 9:50:32'
union all select '2006-7-10 9:50:32'

select * from t

--只看天数
declare @date datetime
set @date='2006-7-10 9:50:32'
delete from t where datediff(d,date1,@date) > 5
select * from t

--只看年月日（只看天数）
declare @date datetime
set @date='2006-7-10 9:50:32'
delete from t where convert(varchar(10),date1,120) < convert(varchar(10),@date-5,120)
select * from t

--以整个时间来算5天
declare @date datetime
set @date='2006-7-10 9:50:32'

delete from t where date1 < @date-5

select * from t

 10. 给选出的记录集加ID
select id=identity(int,1,1),* into #T from customer
select * from #T
drop table #T

select id=(select count(*) from customer where Name<=tem.Name),* from customer tem
--此处Name要有顺序，而且不能有重复值