SQL server---学习记录

declare @Colstr varchar(100)
select @Colstr = isnull(@Colstr+',', '') + isnull(c.name, '') from syscolumns c inner join sysobjects o on c.id=o.id where o.name='warehouse'
select @Colstr as Colstr
---将表的所有字段并排显示。

 

SQL-server 
select a.Name as tableName from sysobjects a inner join syscolumns b on a.ID=b.ID
where b.name = '字段名'

Mysql 

use information_schema;
select * from columns where column_name='字段名'

 ---根据字段来查相应的表。

DROP PROCEDURE IF EXISTS PR_update_AUTO_INCREMENT;
DELIMITER $$
CREATE PROCEDURE PR_update_AUTO_INCREMENT()
BEGIN
DECLARE V_sqlStr VARCHAR(100);
DECLARE i INT DEFAULT 1;
DECLARE V_id INT;
DECLARE v_finished INT DEFAULT 0;
DECLARE update_cursor CURSOR FOR SELECT ID FROM TableName ORDER BY ID;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = 1;

OPEN update_cursor;
REPEAT
FETCH update_cursor INTO V_id;
IF NOT v_finished THEN
UPDATE TableName SET ID=i WHERE ID=V_id;
SET i=i+1;
END IF;
UNTIL v_finished
END REPEAT;
CLOSE update_cursor;

SET V_sqlStr= CONCAT('ALTER TABLE TableName AUTO_INCREMENT = ',i,';');
SET @v_sql=V_sqlStr; -- 注意很重要，将连成成的字符串赋值给一个变量（可以之前没有定义，但要以@开头）
PREPARE stmt FROM @v_sql;-- 预处理需要执行的动态SQL，其中stmt是一个变量
EXECUTE stmt; -- 执行SQL语句
DEALLOCATE PREPARE stmt; -- 释放掉预处理段
END$$
DELIMITER ;

--将自增长字段重新排序
CALL PR_update_AUTO_INCREMENT();