基本SQL语句

--提取决策分析报表
p_get_select 'c_function', 'winname=''rw_distapply_m''',1
go
p_get_select 'c_rpt_model', 'rptname=''rw_distapply_m''',1
go
p_get_select 'c_rpt_col', 'rptname=''rw_distapply_m''',1
go
p_get_select 'c_rpt_group', 'rptname=''rw_distapply_m''',1
go
p_get_select 'c_rpt_tbl', 'rptname=''rw_distapply_m''',1
go
p_get_select 'c_retrieve', 'funcno=''rw_distapply_m''',1


---提取动态报表
p_get_select 'c_function', 'winname=''dw_sale_i_tht''', 1
go
p_get_select 'c_dyreport', 'dwname=''dw_sale_i_tht''', 1
go
p_get_select 'c_dyscript', 'winname=''dw_sale_i_tht''', 1
go
p_get_select 'c_relation', 'srcwinname=''dw_sale_i_tht''', 1
go
p_get_select 'c_retrieve', 'funcno=''dw_sale_i_tht''', 1
go




sp_spaceused u_sale_c 查单个表的空间

查数据库表的空间
declare @name varchar(1000)
declare @sql varchar(1000)


if object_id('tempdb..#space') is not null drop table #space
create table #space(name varchar(50),rows bigint,reserved varchar(12),data varchar(12),index_size varchar(12),unused varchar(12))

declare sp cursor local for select '['+name+']' from sysobjects where type = 'u'
open sp
fetch sp into @name
while @@fetch_status = 0
begin
set @sql = 'insert into #space exec sp_spaceused ' + @name
exec(@sql)
fetch sp into @name
end
close sp

select * from #space order by cast(replace(reserved,'kb','')as int) desc

SQL2005,2008清日志语句
USE [master]
GO
ALTER DATABASE hydee SET RECOVERY SIMPLE WITH NO_WAIT
GO
ALTER DATABASE hydee SET RECOVERY SIMPLE
GO
USE hydee
go
declare @name varchar(100)

select top 1 @name = name from sysfiles where status = 1048642

DBCC SHRINKFILE (@name , 11, TRUNCATEONLY)
GO
USE [master]
GO
ALTER DATABASE hydee SET RECOVERY FULL WITH NO_WAIT

GO
ALTER DATABASE hydee SET RECOVERY FULL
GO




select @@version 查数据版本

dbcc checkdb 检查数据库
dbcc dbreindex('u_ware_q') 重建索引

dbcc checktable('u_ware_q')检查索引


p_dboptimize 重建所有索引过程


select *from c_columns where colname ='wareid' 查表字段中文名


select *from c_columns where tabname ='u_ware_q'查表有那些字段

select *from c_objects where name='u_store_c'查表名


select OBJECT_NAME(id),* from syscomments where text like '%r_store_nosale%'查询某个表在哪些过程,触发器视图，里面出现过。


select *from sysobjects where type='p' and name like 'p_prom%' 查找数据的存储过程