select DATEPART(m , getdate()) as 月份,datediff(dd , getdate(), dateadd(mm, 1,getdate())) as 天数

显示某一个月的天数

select datepart(wk,getdate() )- datepart(wk,'2008-2-3' )

显示某一天距离当前的周数

--本周的星期一
SELECTDATEADD(wk,DATEDIFF(wk,0,getdate()),0)

--本周的星期日
SELECTDATEADD(wk,DATEDIFF(wk,0,getdate()),0)-1

create proc p
@month char(10)
as
declare @day int
declare @i int
declare @sql as varchar(1000)
select @day=datediff(dd ,@month,dateadd(mm, 1,@month))
set @i=1
set @sql='create table #tbl('
while @i<=@day
begin
set @sql=@sql+'tbl'+cast(@i as varchar)+' int ,'
set @i=@i+1
end
set @sql = left(@sql , len(@sql)-1) + ') select * from #tbl'
print @sql
exec(@sql)
go
exec p '2008-2-1'
drop proc p

创建动态列的表

SELECT name FROM sysobjects WHERE xtype= 'U'
union all
select o.name+'总数 = ' + cast(sum(i.rows) as varchar) from sysindexes i,sysobjects o
where i.id = o.id
and o.xtype='U'
and i.indid<2
group by o.name
order by name

某个数据库内所有的表的名字和总记录数