查找数据库里所有表当前的最大id，数据库id
今天要统计一下数据库里所有表当前的最大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
我是这样做的测试：
create table music(
id varchar(10),
title varchar(100),
name varchar(10)
);
insert into music values('2','离别','12354');
insert into music values('15','朋友','5454');
insert into music values('161','送别','4668');
<?php
$conn=mysql_connect("localhost:3307","数据库用户名","数据库密码");
mysql_select_db("date");
$result=mysql_query("select max(id+0) max_id from music",$conn);
$field=mysql_fetch_row($result);
print_r($field);
?>
结果：Array ( [0] => 161 )
因为mysql中varchar不能用max()所以通过id+0来转换id类型从而解决这个问题 ，如果你建表时id是整数型的就可以直接用max()了。详细解释见：hb.qq.com/a/20110624/000061.htm。
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'