Sql Server 常用sql 积累

1. 查询数据库有多少张表
   1、SELECT COUNT(*) FROM sysobjects WHERE (xtype = 'U')
2、select count(1) from sys.tables
2. 查数据库表以及表的数据量
   SELECT a.name,b.rows FROM sysobjects a INNER JOIN sysindexes b ON a.id=b.id WHERE b.indid IN(0,1) AND a.Type='u' ORDER BY a.name
3. 字符串去空格常用的几个函数

1 LTrim(String str) -- 去掉字符串前部分空格
2 RTrim(String str) -- 去掉字符串后部分空格
3 Replace(String str,' ','')--去掉字符串中的空格，将' '替换为''

1. 递归查询

WITH cte(deptid,deptparent) AS (
SELECT deptid,deptparent FROM dbo.SDept WHERE deptid = 1
UNION ALL
SELECT SDept.deptid,SDept.deptparent FROM cte INNER JOIN dbo.SDept on cte.deptid = SDept.deptparent
)
select * from cte