sql常用语句

统计当前数据库有多少张表：

select count(*) from sysobjects where xtype='U'（其它的（如：存储过程，视图......）以此类推）

查询当前表有多少个列（字段）：

select count(name) from syscolumns where id=(select id from sysobjects where xtype='u' and name='表名')

查询哪些数据库包含表名tableName：

sp_MSforeachdb @command1 = 'USE ? if exists(SELECT 1 from sysobjects where id=object_id(''tableName'')) PRINT ''?'''

查询不为null 的信息：

select bmslh ,bjsj from tablename where bjsj is not null

算百分比 ：‘

cast(cast(分子*1.0*100/分母 as decimal(10,2)) as varchar(50)) +'%' 

根据ip排序：

 

SELECT [IPAddress] FROM [WorkStation]
ORDER BY CAST(PARSENAME([IPAddress], 4) AS INT),
         CAST(PARSENAME([IPAddress], 3) AS INT),
         CAST(PARSENAME([IPAddress], 2) AS INT),
         CAST(PARSENAME([IPAddress], 1) AS INT)

备份数据库 ：

DECLARE @strPath NVARCHAR(200) 
set @strPath = convert(VARCHAR(10),getdate(),120) 
set @strPath = REPLACE(@strPath, ':' , '-') 
set @strPath = 'D:\Data Base\dbbak\' +'fileshare' +@strPath + '.bak' 
BACKUP DATABASE [fileshare] TO DISK = @strPath WITH init , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT




backup database [dbname] to disk = 'savepath' 
with [init | noinit] ,nounload, ( DIFFERENTIAL ),  NAME = 'miaoshu',  NOSKIP ,  STATS = 10,  NOFORMAT'
init:覆盖媒体  noinit:追加到媒体

有 DIFFERENTIAL  是 差异备份 
去掉 DIFFERENTIAL   是完全备份

删除某库里面所有存储过程

use [databasename] --删除当前库里面所有用户存储过程
go

declare @procName varchar(500)
declare cur cursor
    for select [name] from sys.objects where type = 'p'
open cur 
fetch next from cur into @procName
while @@fetch_status = 0
begin
    if @procName <> 'DeleteAllProcedures'
          exec('drop procedure ' + @procName)
    fetch next from cur into @procName
end
close cur
deallocate cur