*FROM sys.indexesWHERE object_id= OBJECT_ID('tabName')
SELECT * FROM sys.sysindexesWHERE id = OBJECT_ID('tabName')
#######################################################################################
查看存储过程/函数定义：
--MySQL
SHOW CREATE{ PROCEDURE | FUNCTION }name ;
SHOW {PROCEDURE | FUNCTION } STATUS[ LIKE 'pattern'];
SELECT * FROM information_schema.RoutinesWHERE ROUTINE_NAME='name';
--SqlServer
exec sp_helpf_getdate
exec sp_helptextf_getdate
SELECT * FROM information_schema.RoutinesWHERE ROUTINE_NAME='name';
存储过程：
--MySQL
DELIMITER //
CREATE PROCEDUREsp_name(IN param1 INT,OUTparam2 INT)
BEGIN
{sql_statement}
END//
DELIMITER ;
CALL sp_name();
DROP PROCEDUREIF EXISTS sp_name;
--SqlServer
CREATE PROCEDUREsp_name(@param1INT,@param2INT OUTPUT)
AS
BEGIN
{sql_statement}
END
GO
EXEC sp_name();
DROP PROCEDUREsp_name;
函数：
--MySQL
DELIMITER //
CREATE FUNCTIONfn_name()
RETURNS Decimal(10,2)
RETURN 3.14;
//
DELIMITER ;
SELECT fn_name();
DROP PROCEDUREIF EXISTS fn_name;
--SqlServer
CREATE FUNCTIONdbo.fn_name()
RETURNS Decimal(10,2)
AS
BEGIN
RETURN 3.14
END
GO
SELECT dbo.fn_name();
DROP FUNCTIONdbo.fn_name;
触发器：
--MySQL
DELIMITER //
CREATE TRIGGERtr_name
{ AFTER| BEFORE } { INSERT| UPDATE | DELETE }
ON tabName
FOR EACHROW
BEGIN
{sql_statement;}
END
DELIMITER ;
DROP TRIGGER IF EXISTS tr_name;
--SqlServer
CREATE TRIGGERdbo.tr_name
ON [dbo].[tabName]
{ FOR| AFTER | INSTEAD OF} { INSERT | UPDATE| DELETE }
AS
BEGIN
{sql_statement;}
END
GO
DROP TRIGGER dbo.tr_name
#######################################################################################
循环语句：
--MySQL（1至100之和）
WHILE 循环：
DELIMITER //
CREATE PROCEDURETESTPRO()
BEGIN
DECLARE I,K INTDEFAULT 0 ;
WHILE I<= 100 DO
SET K= I + K;
SET I= I + 1;
END WHILE;
SELECT K;
END;//
DELIMITER ;
REPEAT UNTIL 循环：
DELIMITER //
CREATE PROCEDURETESTPRO()
BEGIN
DECLARE I,K INTDEFAULT 0 ;
REPEAT
SET K= I + K;
SET I= I + 1;
UNTIL I>100
END REPEAT;
SELECT K;
END;//
DELIMITER ;
LOOP 循环：
DELIMITER //
CREATE PROCEDURETESTPRO()
BEGIN
DECLARE I,K INTDEFAULT 0 ;
LABEL: LOOP
SET K= I + K;
SET I= I + 1;
IF I>100THEN
LEAVE LABEL;
END IF;
END LOOP;
SELECT K;
END;//
DELIMITER ;
CALL TESTPRO();
DROP PROCEDUREIF EXISTS TESTPRO;
--SqlServer
DECLARE @I INT = 1
DECLARE @K INT = 0
WHILE @I<= 100
BEGIN
SET @K = @K + @I
SET @I = @I + 1
END
SELECT @K
游标：
--MySQL(参数名称不能与列明一样)
DELIMITER //
CREATE PROCEDURETESTPRO()
BEGIN
DECLARE FName varchar(50);
DECLARE LName varchar(50);
DECLARE IsDone BOOLEAN DEFAULTFALSE;
DECLARE cursor_name CURSOR FOR SELECT FirstName,LastNameFROM Person;
DECLARE CONTINUE HANDLER FOR NOT FOUNDSET IsDone = TRUE;
OPEN cursor_name;
LABEL: LOOP
FETCH cursor_name INTO FName,LName;
IF IsDoneTHEN
LEAVE LABEL;
END IF;
SELECT CONCAT('Contact Name:',FName,LName)AS Name;
END LOOP;
CLOSE cursor_name;
END;//
DELIMITER ;
--SqlServer
DECLARE @LastName varchar(50),@FirstName varchar(50);
DECLARE contact_cursor CURSOR FOR SELECT LastName,FirstName FROM Person
OPEN contact_cursor;
FETCH NEXTFROM contact_cursorINTO @LastName,@FirstName;
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT 'Contact Name: ' + @FirstName + ' '+ @LastName
FETCH NEXT FROM contact_cursorINTO @LastName,@FirstName;
END
CLOSE contact_cursor;
DEALLOCATE contact_cursor;
GO
####################################################