Find the current maximum id of all tables in the database

Today, we need to count the current maximum IDs of all tables in the database. It is too difficult to query each table one by one, so we can write a stored procedure for future use. CREATEPROCEDURE [dbo]. [get_tableid] ASCREATETABLE # tablespaceinfo -- create a result storage table (nameinfovarchar (50), max_idinfoint) DECLARE @ ta

Today, we need to count the current maximum IDs of all tables in the database. It is too difficult to query each table one by one, so we can write a stored procedure for future use. Create procedure [dbo]. [get_tableid] as create table # tablespaceinfo -- CREATE a result storage TABLE (nameinfo varchar (50), max_idinfo int) DECLARE @ ta

Today, we need to count the current maximum IDs of all tables in the database. It is too difficult to query each table one by one, so we can write a stored procedure for future use.

Create procedure [dbo]. [get_tableid]

Create table # tablespaceinfo -- CREATE a result storage TABLE
(Nameinfo varchar (50 ),
Max_idinfo int)


DECLARE @ tablename varchar (255) -- table name
DECLARE @ max_idinfo int


DECLARE Info_cursor CURSOR
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