记录几个好用的SQL

--查询表结构

SELECT sys.syscolumns.NAME 字段,sys.systypes.NAME  类型, sys.syscolumns.length 长度 FROM SysColumns INNER JOIN  systypes    ON  sys.syscolumns.xtype = sys.systypes.xtype
WHERE  syscolumns.id=object_id('表名') AND  sys.systypes.NAME<>'sysname' 

--获取存储过程内容
select text from syscomments WITH(NOLOCK) where  id=object_id('存储过程')

--查询数据库中存储过程
select name from QWMS_CSMC.dbo.sysobjects WITH(NOLOCK) where   xtype='P'

--数据库分页
SELECT TOP (10) * -- 10 pageSize
    FROM ( SELECT row_number() OVER (ORDER BY EmployeeBasic .UID ASC )  [row_number],* FROM [dbo].[EmployeeBasic] 
    )  AS temp
    WHERE temp.[row_number] > 1*10 --pageIndex*pageSize
    ORDER BY temp .UID ASC

删除重复

重复数据：

重复数据

--排除重复数据 关键点：有一个能够排序字段，然后根据字段去排除大于部分或者小于部分数据
SELECT id,zexno,tknum FROM [dbo].[Z_SD_RFC_SENDDATA_EC] AS Z WHERE ZEXNO ='0003415668'
 AND EXISTS (SELECT 'Y'  FROM  [Z_SD_RFC_SENDDATA_EC] AS T WHERE T.TKNUM =Z.TKNUM AND T.ZEXNO=Z.ZEXNO
 AND T.ID>Z.ID)

得到结果：

结果