Find the current maximum id and Database id of all tables in the database.

Find the current maximum id and Database 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.

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
In PHP code, how can I query the MYSQL database to obtain the ID number of the largest record in a field in the data table? For example: See Question supplement.

I did the following test:
Create table music (
Id varchar (10 ),
Title varchar (100 ),
Name varchar (10)
);
Insert into music values ('2', 'parting ', '123 ');
Insert into music values ('15', 'friends', '123 ');
Insert into music values ('20160301', 'farewell ', '20160301 ');

<? Php
$ Conn = mysql_connect ("localhost: 3307", "database username", "Database Password ");
Mysql_select_db ("date ");
$ Result = mysql_query ("select max (id + 0) max_id from music", $ conn );
$ Field = mysql_fetch_row ($ result );
Print_r ($ field );
?>
Result: Array ([0] => 161)
Because mysql varchar does not use max (), id + 0 is used to convert the id type to solve this problem. If the id is an integer type during table creation, you can directly use max (). For more information, see hb.qq.com/a/20151124/41061.htm.

The database queries the names of all tables in a database that contain a field.

Select d. name as table_name, a. name as column_name from syscolumns a inner join sysobjects d on a. id = d. id and d. xtype = 'U'
Where a. name = 'stu _ id'