一些实用SQL (持续更新)-优快云博客

--SQL 查询所有表名：
SELECT NAME FROM SYSOBJECTS WHERE TYPE='U'
SELECT * FROM INFORMATION_SCHEMA.TABLES
--查询表的所有字段名：
SELECT NAME FROM SYSCOLUMNS WHERE ID=OBJECT_ID('表名' )
SELECT * FROM INFORMATION_SCHEMA.TABLES
SELECT * FROM INFORMATION_SCHEMA.VIEWS
SELECT * FROM INFORMATION_SCHEMA.COLUMNS

　　分页：

--CREATE DATABASE DB_PageTest
--GO

--USE DB_PageTest
--CREATE TABLE T_Test(
--    ID INT PRIMARY KEY IDENTITY(1,1),
--    Title NVARCHAR(50),
--    TimeSpan Date
--)
--GO

--DECLARE @i INT
--SET @i=0
--WHILE(@i @PageSize*(@PageIndex-1)

--TOP N NOT IN 有自增ID的情况下,SQLSERVER版本为2000的分页
     --SELECT TOP(@PageSize) * FROM T_Test WHERE ID NOT IN
    --(SELECT TOP(@PageSize*(@PageIndex-1)) ID FROM T_Test ORDER BY ID )

--TOP N MAX 有自增ID的情况下,SQLSERVER版本为2000的分页
    SELECT TOP(@PageSize) * FROM T_Test WHERE ID>
    (SELECT MAX(ID) FROM(SELECT TOP(@PageSize * @PageIndex) ID FROM T_Test ORDER BY ID)Tmp)
END
GO
EXEC P_GetPagedTitle 20,1,0

　　Row_Number分页:

1 SELECT * FROM(
2 SELECT *, ROW_NUMBER() OVER(ORDER BY ID)) AS RowNumber  
3   FROM [Table]) t WHERE RowNumber BETWEEN 1 AND 15

转载于:https://www.cnblogs.com/wjn2010/archive/2012/02/15/2352404.html