优软DMS常见维护SQL操作命令

admin

--查看表名和对应的数据行数
select  a.name as '表名',b.rows as '表数据行数'
from sysobjects a inner join sysindexes b
on a.id = b.id
where   a.type = 'u'
and b.indid in (0,1)
--and a.name not like 't%'
order by b.rows desc

admin

--把到期的会员卡自动延期一年。
ALTER TABLE UT_X_Customer DISABLE TRIGGER all--禁用某个表上的所有触发器
update UT_G_Member set VipEDate=dateadd(year,1,VipEDate) where VipEDate<=getdate()+1 and VipNo>'' and VipEDate>''
ALTER TABLE UT_X_Customer enable TRIGGER all--启用某个表上的所有触发器


--把到期的服务卡自动延期一年。
ALTER TABLE UT_X_CardBuy DISABLE TRIGGER all--禁用某个表上的所有触发器
update UT_X_CardBuy set EndDate=dateadd(year,1,EndDate) where EndDate<=getdate()+1 and VipNo>'' and EndDate>''
ALTER TABLE UT_X_CardBuy enable TRIGGER all--启用某个表上的所有触发器

admin

--查询SQL数据库中每个表在数据大小
SELECT   'select *  from '+a.name+'   --'+ltrim(str(b.rows))+'条记录'
FROM      sysobjects AS a INNER JOIN
                 sysindexes AS b ON a.id = b.id
WHERE   (a.type = 'u') AND (b.indid IN (0, 1))
ORDER BY b.rows DESC

admin

--查询SQL数据库中每个表在数据大小
SELECT   'select *  from '+a.name+'   --'+ltrim(str(b.rows))+'条记录'
FROM      sysobjects AS a INNER JOIN
                 sysindexes AS b ON a.id = b.id
WHERE   (a.type = 'u') AND (b.indid IN (0, 1))
ORDER BY b.rows DESC

admin

--SQL Server：统计数据库中每张表的大小
SELECT DISTINCT a.name ,
b.rows
FROM sysobjects a ,
sysindexes b
WHERE a.id = b.id
AND a.type = 'u' AND b.rows>0
ORDER BY b.rows DESC