查找数据库里所有表当前的最大id

来源：互联网发布：夜梦巴黎知乎编辑：程序博客网时间：2024/05/05 09:41

今天要统计一下数据库里所有表当前的最大id，一个一个查太麻烦了，就写了一个存储过程，方便今后继续使用。

CREATE PROCEDURE [dbo].[get_tableid] AS

CREATE TABLE #tablespaceinfo --创建结果存储表
(nameinfo varchar(50) ,
max_idinfo int )

DECLARE @tablename varchar(255) --表名称
DECLARE @max_idinfo int

DECLARE Info_cursor CURSOR FOR
SELECT o.name
FROM dbo.sysobjects o WHERE OBJECTPROPERTY(o.id, N'IsTable') = 1
and o.name not like N'#%%' ORDER BY o.name

OPEN Info_cursor

FETCH NEXT FROM Info_cursor
INTO @tablename

WHILE @@FETCH_STATUS = 0
BEGIN
if exists (select * from dbo.sysobjects where id = object_id(@tablename) and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
SELECT @max_idinfo=IDENT_CURRENT(@tablename)
INSERT #tablespaceinfo (nameinfo,max_idinfo)
VALUES (@tablename,@max_idinfo)
END

FETCH NEXT FROM Info_cursor
INTO @tablename
END
CLOSE Info_cursor
DEALLOCATE Info_cursor
SELECT * FROM #tablespaceinfo ORDER BY nameinfo DESC

3 0