SQL Server/テーブル行数一覧
をテンプレートにして作成
Search in
this wiki
and
or
[
トップ
] [
新規
|
一覧
|
単語検索
|
最終更新
|
ヘルプ
]
開始行:
SQL Serverで、特定のDB内のテーブルを一覧にし、それらの...
** テーブル行数一覧 [#t7ee2b8b]
CREATE TABLE #ROWCOUNTS
(
TNAME varchar(255),
CNT bigint
)
DECLARE @name nvarchar(255)
DECLARE @sql nvarchar(255)
DECLARE TCUR CURSOR FOR
SELECT name FROM sysobjects WHERE type = N'U'
OPEN TCUR
FETCH NEXT FROM TCUR INTO @name
WHILE (@@fetch_status <> -1)
BEGIN
SET @sql = 'INSERT into #ROWCOUNTS SELECT ''' + @name
+ ''' AS TNAME, count(*) as CNT FROM '+ @name
EXEC(@sql)
FETCH NEXT FROM TCUR INTO @name
END
CLOSE TCUR
DEALLOCATE TCUR
select sum(CNT) as row_count, count(*) as table_count fr...
select * from #ROWCOUNTS order by TNAME
DROP TABLE #ROWCOUNTS
** DB一覧 [#h6a0cdb5]
select * from [master].[sys].[databases]
終了行:
SQL Serverで、特定のDB内のテーブルを一覧にし、それらの...
** テーブル行数一覧 [#t7ee2b8b]
CREATE TABLE #ROWCOUNTS
(
TNAME varchar(255),
CNT bigint
)
DECLARE @name nvarchar(255)
DECLARE @sql nvarchar(255)
DECLARE TCUR CURSOR FOR
SELECT name FROM sysobjects WHERE type = N'U'
OPEN TCUR
FETCH NEXT FROM TCUR INTO @name
WHILE (@@fetch_status <> -1)
BEGIN
SET @sql = 'INSERT into #ROWCOUNTS SELECT ''' + @name
+ ''' AS TNAME, count(*) as CNT FROM '+ @name
EXEC(@sql)
FETCH NEXT FROM TCUR INTO @name
END
CLOSE TCUR
DEALLOCATE TCUR
select sum(CNT) as row_count, count(*) as table_count fr...
select * from #ROWCOUNTS order by TNAME
DROP TABLE #ROWCOUNTS
** DB一覧 [#h6a0cdb5]
select * from [master].[sys].[databases]
ページ名:
![[Return to Top Page]](image//logo.png "[Return to Top Page]"){kind=logo}
