一些实用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

 

posted @ 2012-02-15 13:04  wjn2010  阅读(298)  评论(0编辑  收藏  举报