Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Получение списка всех таблиц на сервере?  [new]
teCa
Member

Откуда: Москва
Сообщений: 893
Всем привет, ребят, подскажите по поводу скрипта плз.

Хочу получить размеры таблиц для всех БД на сервере, погуглил, и нашел вот такой запрос:

CREATE TABLE #t([имя таблицы] varchar(255), [строк] varchar(255), [зарезервировано] varchar(255), [всего данных] varchar(255), [размер индексов] varchar(255), [свободно] varchar(255));
	INSERT INTO #t
	exec sp_msforeachtable N'exec sp_spaceused ''?''';
	SELECT * FROM #t ORDER BY CONVERT(bigint, REPLACE([всего данных], ' KB', '')) DESC;
	DROP TABLE #t;


Далее решил "обернуть" данный скрипт в курсор и пройтись во всем БД на сервере, получилось следующее:

USE master;
GO
DECLARE contact_cursor CURSOR FOR
SELECT name
FROM sys.databases ;
GO
OPEN contact_cursor;
declare @name varchar(50);
FETCH NEXT FROM contact_cursor
INTO @name;
WHILE @@FETCH_STATUS = 0
BEGIN
	exec('USE '+@name + ';')
	DBCC UPDATEUSAGE (0);
	CREATE TABLE #t([имя таблицы] varchar(255), [строк] varchar(255), [зарезервировано] varchar(255), [всего данных] varchar(255), [размер индексов] varchar(255), [свободно] varchar(255));
	INSERT INTO #t
	exec sp_msforeachtable N'exec sp_spaceused ''?''';
	SELECT * FROM #t ORDER BY CONVERT(bigint, REPLACE([всего данных], ' KB', '')) DESC;
	DROP TABLE #t;
   
   FETCH NEXT FROM contact_cursor;
END

CLOSE contact_cursor;
DEALLOCATE contact_cursor;
GO


Но похоже что-то я не учел, можете подсказать, что именно?
3 сен 13, 10:54    [14788147]     Ответить | Цитировать Сообщить модератору
 Re: Получение списка всех таблиц на сервере?  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
а зачем ВСЕ таблицы во ВСЕХ бд ?
3 сен 13, 10:55    [14788154]     Ответить | Цитировать Сообщить модератору
 Re: Получение списка всех таблиц на сервере?  [new]
Алексей Куренков
Member [заблокирован]

Откуда: Москва
Сообщений: 567
teCa,

попробуйте так.

declare @t table ([БД] varchar(255) default db_name(), [имя таблицы] varchar(255), [строк] varchar(255), [зарезервировано] varchar(255), [всего данных] varchar(255), [размер индексов] varchar(255), [свободно] varchar(255))

insert @t ([имя таблицы], [строк], [зарезервировано], [всего данных], [размер индексов], [свободно])
exec sp_msforeachdb N'use [?]; if db_id(''?'')>4 exec sp_msforeachtable N''exec sp_spaceused ''''&'''''',''&'''

select * from @t order by convert(bigint, replace([всего данных], ' kb', '')) desc;
3 сен 13, 11:02    [14788193]     Ответить | Цитировать Сообщить модератору
 Re: Получение списка всех таблиц на сервере?  [new]
Гость333
Member

Откуда:
Сообщений: 3683
teCa
	exec('USE '+@name + ';')

Но похоже что-то я не учел, можете подсказать, что именно?

Вы не учли область действия "'USE @name". В данном случае она ограничивается динамическим SQL.
3 сен 13, 11:06    [14788217]     Ответить | Цитировать Сообщить модератору
 Re: Получение списка всех таблиц на сервере?  [new]
Алексей Куренков
Member [заблокирован]

Откуда: Москва
Сообщений: 567
Алексей Куренков,

Сори за некорректный скрипт - подправил:

if object_id('tempdb..#t','U') is not null drop table #t
create table #t (db varchar(255) default db_name(), table_name varchar(255), [rows] varchar(255), reserved varchar(255), data varchar(255), indexes varchar(255), unused varchar(255))

exec sp_msforeachdb N'use [?];
if db_id(''?'')>4 insert #t (table_name,[rows],reserved,data,indexes,unused) exec sp_msforeachtable N''exec sp_spaceused ''''&'''''',''&'''

select * from #t order by convert(bigint, replace(data, ' kb', '')) desc;
drop table #t
3 сен 13, 11:14    [14788271]     Ответить | Цитировать Сообщить модератору
 Re: Получение списка всех таблиц на сервере?  [new]
teCa
Member

Откуда: Москва
Сообщений: 893
Алексей Куренков
Алексей Куренков,

Сори за некорректный скрипт - подправил:

if object_id('tempdb..#t','U') is not null drop table #t
create table #t (db varchar(255) default db_name(), table_name varchar(255), [rows] varchar(255), reserved varchar(255), data varchar(255), indexes varchar(255), unused varchar(255))

exec sp_msforeachdb N'use [?];
if db_id(''?'')>4 insert #t (table_name,[rows],reserved,data,indexes,unused) exec sp_msforeachtable N''exec sp_spaceused ''''&'''''',''&'''

select * from #t order by convert(bigint, replace(data, ' kb', '')) desc;
drop table #t


Спасибо, то что нужно.
3 сен 13, 15:01    [14789714]     Ответить | Цитировать Сообщить модератору
 Re: Получение списка всех таблиц на сервере?  [new]
Dmitriyv
Member

Откуда:
Сообщений: 68
teCa, что то я делаю не так

Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 31
Could not complete cursor operation because the set options have changed since the cursor was declared.
Msg 16917, Level 16, State 2, Procedure sp_MSforeach_worker, Line 32
Cursor is not open.
Msg 16917, Level 16, State 1, Procedure sp_MSforeach_worker, Line 153
Cursor is not open.

(478 row(s) affected)
5 сен 13, 09:59    [14797527]     Ответить | Цитировать Сообщить модератору
 Re: Получение списка всех таблиц на сервере?  [new]
Dmitriyv
Member

Откуда:
Сообщений: 68
teCa, упс мой косяк :) все работает отлично
5 сен 13, 10:19    [14797675]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить