SQL获取每张物理表大小/获取上下级/删除临时表/自动编码
记录下最近用到的几个SQL：
一：获取数据库每张物理表大小，在迁移数据库时可以用来清除冗余数据，不然数据库太大总是不方便呀
create table tmp ( name varchar(50), rowscount int, reserved varchar(50), data varchar(50), index_size varchar(50), unused varchar(50) ); insert tmp(name, rowscount, reserved, data, index_size, unused) exec sp_MSforeachtable @command1="sp_spaceused '?'"; select * from tmp where name <> 'tmp' order by rowscount desc drop table tmp ;
二：获取所有上级/下级
with cte as ( select Id,Pid,DeptName, 0 as lvl from Department where Id = 2 union all select d.Id,d.Pid,d.DeptName,lvl + 1 from cte c inner join Department d on c.Id = d.Pid ) select * from cte
【可忽略】然后这个是我的实例代码：
with cte as ( select OrgUnitGUID,OrgUnitCode,OrgUnitName,OrgUnitLever,DimensionCode,DimensionName from dbo.MDM_OrganizationUnit where OrgUnitGUID = '431213' union all select c.OrgUnitGUID,c.OrgUnitCode,c.OrgUnitName,c.OrgUnitLever,c.DimensionCode,c.DimensionName from cte a inner join dbo.MDM_Organization_Link b on b.OrgUnitGuid = a.OrgUnitGUID inner join dbo.MDM_OrganizationUnit c on c.OrgUnitGUID = b.ParentOrgUnitGuid ) select * from cte where OrgUnitLever = '3'
【可忽略】然后这个是更复杂的一个实例中的代码：
if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#temp1')) drop table #temp1 --用户所属组织和所有下级组织 ;with cte as ( select OrgUnitGUID from dbo.MDM_OrganizationUnit where OrgUnitGUID in ( select po.OrgUnitGuid from dbo.MDM_User u left join dbo.MDM_User_Position_Link up on up.UserGUID = u.UserID left join dbo.MDM_PostOrganization_Link po on po.PositionGuid = up.PositionGUID where u.Userloginid=@userloginid ) union all select c.OrgUnitGUID from cte a inner join dbo.MDM_Organization_Link b on b.ParentOrgUnitGuid = a.OrgUnitGUID inner join dbo.MDM_OrganizationUnit c on c.OrgUnitGUID = b.OrgUnitGuid ) --组织下所有有效用户 select * into #temp1 from ( select Userloginid,Password,Username,Email,Mobilephone,Mobilephone2,Wechat,Businesstelephone ,u.Status,Gender,Gendertext,Birthday,Joindate,Workdate,NationalityCode,Nationalityname ,NationCode,Nationname,NativePlaceCode,Nativeplacename,PaperTypeCode,Papertypename,Papernumber ,EducationCode,Educationname,MaritalStatusCode,Maritalstatusname,Image,Interest,Motto,u.CreateDate ,o.OrgUnitName,p.PositionName from dbo.MDM_User u inner join dbo.MDM_User_Position_Link up on up.UserGUID = u.UserID inner join dbo.MDM_PostOrganization_Link po on po.PositionGuid = up.PositionGUID inner join dbo.MDM_OrganizationUnit o on o.OrgUnitGUID = po.OrgUnitGuid inner join dbo.MDM_Position p on p.PositionGUID = po.PositionGuid where u.Status=1 and u.MdmStatus=1 and po.OrgUnitGuid in (select OrgUnitGUID from cte) )a --拼接组织和岗位字符串 select * from( select ROW_NUMBER() OVER(ORDER BY CreateDate desc) as RowNumber,* --,COUNT(1) OVER() AS TOTAL_COUNT from ( SELECT distinct Userloginid,Password,Username,Email,Mobilephone,Mobilephone2,Wechat,Businesstelephone ,Status,Gender,Gendertext,Birthday,Joindate,Workdate,NationalityCode,Nationalityname ,NationCode,Nationname,NativePlaceCode,Nativeplacename,PaperTypeCode,Papertypename,Papernumber ,EducationCode,Educationname,MaritalStatusCode,Maritalstatusname,Image,Interest,Motto,CreateDate ,Organization=STUFF((SELECT ','+OrgUnitName FROM #temp1 t WHERE Userloginid=tb.Userloginid FOR XML PATH('')), 1, 1, '') ,Position=STUFF((SELECT ','+PositionName FROM #temp1 t WHERE Userloginid=tb.Userloginid FOR XML PATH('')), 1, 1, '') FROM #temp1 tb GROUP BY Userloginid,Password,Username,Email,Mobilephone,Mobilephone2,Wechat,Businesstelephone ,Status,Gender,Gendertext,Birthday,Joindate,Workdate,NationalityCode,Nationalityname ,NationCode,Nationname,NativePlaceCode,Nativeplacename,PaperTypeCode,Papertypename,Papernumber ,EducationCode,Educationname,MaritalStatusCode,Maritalstatusname,Image,Interest,Motto,CreateDate ,OrgUnitName,PositionName )b )c where c.RowNumber <= @rows if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#temp1')) drop table #temp1
三：删除临时表
if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#temp1')) drop table #temp1
四：自动编码：逻辑如下
人员：上游系统编码除外，本系统新增数据以MU开头，加上四位自增长数字
职位：上游系统编码除外，本系统新增数据以MP开头，加上四位自增长数字
组织：组织编码是在父组织编码的基础上，多加两位自增长数字；若本系统新增组织则加上前缀MO；若同步上游系统组织，也自动在子组织加上前缀MO
最后只要有MO前缀的均为本系统新增组织
/// <summary> /// 获取自动生成的系统编码 /// </summary> /// <param name="type">类型：1=组织，2=岗位，3=人员</param> /// <param name="upOrgID">上级组织ID</param> /// <returns></returns> public static string GetSysAutoCode(string type,string upOrgID) { var code = string.Empty; using (DataAccessBroker broker = DataAccessFactory.Instance()) { var strSql = string.Empty; DataAccessParameterCollection dpc = new DataAccessParameterCollection(); //人员 if (type == "3") { strSql = @"declare @Code varchar(6) select @Code = 'MU'+RIGHT('0000'+CAST( temp.num AS nvarchar(50)),4) from( select top 1 CONVERT(int,RIGHT(UserCode,4)) + 1 as num from dbo.MDM_User where UserCode like 'MU%' order by UserCode desc )temp if(@Code is null or @Code = '') set @Code = 'MU0001' select @Code as Code"; } //岗位 else if (type == "2") { strSql = @"declare @Code varchar(6) select @Code = 'MP'+RIGHT('0000'+CAST(temp.num AS nvarchar(50)),4) from( select top 1 CONVERT(int,RIGHT(PositionCode,4)) + 1 as num from dbo.MDM_Position where PositionCode like 'MP%' order by PositionCode desc )temp if(@Code is null or @Code = '') set @Code = 'MP0001' select @Code as Code"; } //组织 else if(type == "1") { strSql = @"declare @Code varchar(2),@upCode varchar(10) --母编码 select @upCode = OrgUnitCode from dbo.MDM_OrganizationUnit where OrgUnitGUID=@upOrgID if(PATINDEX ( '%MO%' , @upCode ) <= 0) set @upCode = 'MO' + @upCode --子编码 select @Code = RIGHT('00'+CAST( temp.num AS nvarchar(50)),2) from( select top 1 CONVERT(int,RIGHT(o.OrgUnitCode,2)) + 1 as num from dbo.MDM_Organization_Link l inner join dbo.MDM_OrganizationUnit o on o.OrgUnitGUID = l.OrgUnitGuid and o.OrgUnitCode like 'MO%' where l.ParentOrgUnitGuid = @upOrgID order by o.OrgUnitCode desc )temp if(@Code is null or @Code = '') set @Code = '01' select @upCode+@Code as Code"; dpc.AddWithValue("@upOrgID", upOrgID); } DataSet ds = broker.FillSQLDataSet(strSql, dpc); code = ds.Tables[0].Rows[0][0].ToString(); } return code; }
