MS SQL

Текущая версия на 16:16, 27 июля 2014

[править] Подсчет числа записей в таблице

SELECT sc.name +'.'+ ta.name TableName, SUM(pa.rows) RowCnt
FROM sys.tables ta
    INNER JOIN sys.partitions pa ON pa.OBJECT_ID = ta.OBJECT_ID
    INNER JOIN sys.schemas sc ON ta.schema_id = sc.schema_id
WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0)
GROUP BY sc.name,ta.name
ORDER BY SUM(pa.rows) DESC

[править] Деление таблиц на страницы

http://habrahabr.ru/post/126770/

declare @pagenumber int, @pagesize int
set @pagesize=10
set @pagenumber=2

;with rowpaging as (select ROW_NUMBER() over(order by name) as rn, object_id from sys.tables)
select rn, name from sys.tables as m JOIN rowpaging as r ON r.object_id = m.object_id
where rn between @pagesize*(@pagenumber-1)+1 and @pagesize*@pagenumber
order by m.name