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

Source: Internet
Author: User

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'

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.