首页 > 代码库 > 生成存储过程代码
生成存储过程代码
下面是自动产生存储过程的sql 脚本,你可以在查询分析器中运行. 运行完,你会看到多了四个存储过程 pr__SYS_MakeInsertRecordProc pr__SYS_MakeUpdateRecordProc pr__SYS_MakeSelectRecordProc pr__SYS_MakeDeleteRecordProc 执行方式:在查询分析器中执行 pr__SYS_MakeInsertRecordProc ‘表名‘ --得到插入语句 pr__SYS_MakeInsertRecordProc ‘表名‘,1 --得到插入语句,并创建Insert存储过程 完整脚本如下: SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO CREATE PROC pr__SYS_MakeDeleteRecordProc @sTableName varchar(128), @bExecute bit = 0 AS IF dbo.fnTableHasPrimaryKey(@sTableName) = 0 BEGIN RAISERROR (‘Procedure cannot be created on a table with no primary key.‘, 10, 1) RETURN END DECLARE @sProcText varchar(8000), @sKeyFields varchar(2000), @sWhereClause varchar(2000), @sColumnName varchar(128), @nColumnID smallint, @bPrimaryKeyColumn bit, @nAlternateType int, @nColumnLength int, @nColumnPrecision int, @nColumnScale int, @IsNullable bit, @IsIdentity int, @sTypeName varchar(128), @sDefaultValue varchar(4000), @sCRLF char(2), @sTAB char(1) SET @sTAB = char(9) SET @sCRLF = char(13) + char(10) SET @sProcText = ‘‘ SET @sKeyFields = ‘‘ SET @sWhereClause = ‘‘ SET @sProcText = @sProcText + ‘IF EXISTS(SELECT * FROM sysobjects WHERE name = ‘‘prApp_‘ + @sTableName + ‘_Delete‘‘)‘ + @sCRLF SET @sProcText = @sProcText + @sTAB + ‘DROP PROC prApp_‘ + @sTableName + ‘_Delete‘ + @sCRLF IF @bExecute = 0 SET @sProcText = @sProcText + ‘GO‘ + @sCRLF SET @sProcText = @sProcText + @sCRLF PRINT @sProcText IF @bExecute = 1 EXEC (@sProcText) SET @sProcText = ‘‘ SET @sProcText = @sProcText + ‘----------------------------------------------------------------------------‘ + @sCRLF SET @sProcText = @sProcText + ‘-- Delete a single record from ‘ + @sTableName + @sCRLF SET @sProcText = @sProcText + ‘----------------------------------------------------------------------------‘ + @sCRLF SET @sProcText = @sProcText + ‘CREATE PROC prApp_‘ + @sTableName + ‘_Delete‘ + @sCRLF DECLARE crKeyFields cursor for SELECT * FROM dbo.fnTableColumnInfo(@sTableName) ORDER BY 2 OPEN crKeyFields FETCH NEXT FROM crKeyFields INTO @sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType, @nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable, @IsIdentity, @sTypeName, @sDefaultValue WHILE (@@FETCH_STATUS = 0) BEGIN IF (@bPrimaryKeyColumn = 1) BEGIN IF (@sKeyFields <> ‘‘) SET @sKeyFields = @sKeyFields + ‘,‘ + @sCRLF SET @sKeyFields = @sKeyFields + @sTAB + ‘@‘ + @sColumnName + ‘ ‘ + @sTypeName IF (@nAlternateType = 2) --decimal, numeric SET @sKeyFields = @sKeyFields + ‘(‘ + CAST(@nColumnPrecision AS varchar(3)) + ‘, ‘ + CAST(@nColumnScale AS varchar(3)) + ‘)‘ ELSE IF (@nAlternateType = 1) --character and binary SET @sKeyFields = @sKeyFields + ‘(‘ + CAST(@nColumnLength AS varchar(4)) + ‘)‘ IF (@sWhereClause = ‘‘) SET @sWhereClause = @sWhereClause + ‘WHERE ‘ ELSE SET @sWhereClause = @sWhereClause + ‘ AND ‘ SET @sWhereClause = @sWhereClause + @sTAB + @sColumnName + ‘ = @‘ + @sColumnName + @sCRLF END FETCH NEXT FROM crKeyFields INTO @sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType, @nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable, @IsIdentity, @sTypeName, @sDefaultValue END CLOSE crKeyFields DEALLOCATE crKeyFields SET @sProcText = @sProcText + @sKeyFields + @sCRLF SET @sProcText = @sProcText + ‘AS‘ + @sCRLF SET @sProcText = @sProcText + @sCRLF SET @sProcText = @sProcText + ‘DELETE ‘ + @sTableName + @sCRLF SET @sProcText = @sProcText + @sWhereClause SET @sProcText = @sProcText + @sCRLF IF @bExecute = 0 SET @sProcText = @sProcText + ‘GO‘ + @sCRLF PRINT @sProcText IF @bExecute = 1 EXEC (@sProcText) GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE PROC pr__SYS_MakeInsertRecordProc @sTableName varchar(128), @bExecute bit = 0 AS IF dbo.fnTableHasPrimaryKey(@sTableName) = 0 BEGIN RAISERROR (‘Procedure cannot be created on a table with no primary key.‘, 10, 1) RETURN END DECLARE @sProcText varchar(8000), @sKeyFields varchar(2000), @sAllFields varchar(2000), @sAllParams varchar(2000), @sWhereClause varchar(2000), @sColumnName varchar(128), @nColumnID smallint, @bPrimaryKeyColumn bit, @nAlternateType int, @nColumnLength int, @nColumnPrecision int, @nColumnScale int, @IsNullable bit, @IsIdentity int, @HasIdentity int, @sTypeName varchar(128), @sDefaultValue varchar(4000), @sCRLF char(2), @sTAB char(1) SET @HasIdentity = 0 SET @sTAB = char(9) SET @sCRLF = char(13) + char(10) SET @sProcText = ‘‘ SET @sKeyFields = ‘‘ SET @sAllFields = ‘‘ SET @sWhereClause = ‘‘ SET @sAllParams = ‘‘ SET @sProcText = @sProcText + ‘IF EXISTS(SELECT * FROM sysobjects WHERE name = ‘‘prApp_‘ + @sTableName + ‘_Insert‘‘)‘ + @sCRLF SET @sProcText = @sProcText + @sTAB + ‘DROP PROC prApp_‘ + @sTableName + ‘_Insert‘ + @sCRLF IF @bExecute = 0 SET @sProcText = @sProcText + ‘GO‘ + @sCRLF SET @sProcText = @sProcText + @sCRLF PRINT @sProcText IF @bExecute = 1 EXEC (@sProcText) SET @sProcText = ‘‘ SET @sProcText = @sProcText + ‘----------------------------------------------------------------------------‘ + @sCRLF SET @sProcText = @sProcText + ‘-- Insert a single record into ‘ + @sTableName + @sCRLF SET @sProcText = @sProcText + ‘----------------------------------------------------------------------------‘ + @sCRLF SET @sProcText = @sProcText + ‘CREATE PROC prApp_‘ + @sTableName + ‘_Insert‘ + @sCRLF DECLARE crKeyFields cursor for SELECT * FROM dbo.fnTableColumnInfo(@sTableName) ORDER BY 2 OPEN crKeyFields FETCH NEXT FROM crKeyFields INTO @sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType, @nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable, @IsIdentity, @sTypeName, @sDefaultValue WHILE (@@FETCH_STATUS = 0) BEGIN IF (@IsIdentity = 0) BEGIN IF (@sKeyFields <> ‘‘) SET @sKeyFields = @sKeyFields + ‘,‘ + @sCRLF SET @sKeyFields = @sKeyFields + @sTAB + ‘@‘ + @sColumnName + ‘ ‘ + @sTypeName IF (@sAllFields <> ‘‘) BEGIN SET @sAllParams = @sAllParams + ‘, ‘ SET @sAllFields = @sAllFields + ‘, ‘ END IF (@sTypeName = ‘timestamp‘) SET @sAllParams = @sAllParams + ‘NULL‘ ELSE IF (@sDefaultValue IS NOT NULL) SET @sAllParams = @sAllParams + ‘COALESCE(@‘ + @sColumnName + ‘, ‘ + @sDefaultValue + ‘)‘ ELSE SET @sAllParams = @sAllParams + ‘@‘ + @sColumnName SET @sAllFields = @sAllFields + @sColumnName END ELSE BEGIN SET @HasIdentity = 1 END IF (@nAlternateType = 2) --decimal, numeric SET @sKeyFields = @sKeyFields + ‘(‘ + CAST(@nColumnPrecision AS varchar(3)) + ‘, ‘ + CAST(@nColumnScale AS varchar(3)) + ‘)‘ ELSE IF (@nAlternateType = 1) --character and binary SET @sKeyFields = @sKeyFields + ‘(‘ + CAST(@nColumnLength AS varchar(4)) + ‘)‘ IF (@IsIdentity = 0) BEGIN IF (@sDefaultValue IS NOT NULL) OR (@IsNullable = 1) OR (@sTypeName = ‘timestamp‘) SET @sKeyFields = @sKeyFields + ‘ = NULL‘ END FETCH NEXT FROM crKeyFields INTO @sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType, @nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable, @IsIdentity, @sTypeName, @sDefaultValue END CLOSE crKeyFields DEALLOCATE crKeyFields SET @sProcText = @sProcText + @sKeyFields + @sCRLF SET @sProcText = @sProcText + ‘AS‘ + @sCRLF SET @sProcText = @sProcText + @sCRLF SET @sProcText = @sProcText + ‘INSERT ‘ + @sTableName + ‘(‘ + @sAllFields + ‘)‘ + @sCRLF SET @sProcText = @sProcText + ‘VALUES (‘ + @sAllParams + ‘)‘ + @sCRLF SET @sProcText = @sProcText + @sCRLF IF (@HasIdentity = 1) BEGIN SET @sProcText = @sProcText + ‘RETURN SCOPE_IDENTITY()‘ + @sCRLF SET @sProcText = @sProcText + @sCRLF END IF @bExecute = 0 SET @sProcText = @sProcText + ‘GO‘ + @sCRLF PRINT @sProcText IF @bExecute = 1 EXEC (@sProcText) GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO CREATE PROC pr__SYS_MakeSelectRecordProc @sTableName varchar(128), @bExecute bit = 0 AS IF dbo.fnTableHasPrimaryKey(@sTableName) = 0 BEGIN RAISERROR (‘Procedure cannot be created on a table with no primary key.‘, 10, 1) RETURN END DECLARE @sProcText varchar(8000), @sKeyFields varchar(2000), @sSelectClause varchar(2000), @sWhereClause varchar(2000), @sColumnName varchar(128), @nColumnID smallint, @bPrimaryKeyColumn bit, @nAlternateType int, @nColumnLength int, @nColumnPrecision int, @nColumnScale int, @IsNullable bit, @IsIdentity int, @sTypeName varchar(128), @sDefaultValue varchar(4000), @sCRLF char(2), @sTAB char(1) SET @sTAB = char(9) SET @sCRLF = char(13) + char(10) SET @sProcText = ‘‘ SET @sKeyFields = ‘‘ SET @sSelectClause = ‘‘ SET @sWhereClause = ‘‘ SET @sProcText = @sProcText + ‘IF EXISTS(SELECT * FROM sysobjects WHERE name = ‘‘prApp_‘ + @sTableName + ‘_Select‘‘)‘ + @sCRLF SET @sProcText = @sProcText + @sTAB + ‘DROP PROC prApp_‘ + @sTableName + ‘_Select‘ + @sCRLF IF @bExecute = 0 SET @sProcText = @sProcText + ‘GO‘ + @sCRLF SET @sProcText = @sProcText + @sCRLF PRINT @sProcText IF @bExecute = 1 EXEC (@sProcText) SET @sProcText = ‘‘ SET @sProcText = @sProcText + ‘----------------------------------------------------------------------------‘ + @sCRLF SET @sProcText = @sProcText + ‘-- Select a single record from ‘ + @sTableName + @sCRLF SET @sProcText = @sProcText + ‘----------------------------------------------------------------------------‘ + @sCRLF SET @sProcText = @sProcText + ‘CREATE PROC prApp_‘ + @sTableName + ‘_Select‘ + @sCRLF DECLARE crKeyFields cursor for SELECT * FROM dbo.fnTableColumnInfo(@sTableName) ORDER BY 2 OPEN crKeyFields FETCH NEXT FROM crKeyFields INTO @sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType, @nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable, @IsIdentity, @sTypeName, @sDefaultValue WHILE (@@FETCH_STATUS = 0) BEGIN IF (@bPrimaryKeyColumn = 1) BEGIN IF (@sKeyFields <> ‘‘) SET @sKeyFields = @sKeyFields + ‘,‘ + @sCRLF SET @sKeyFields = @sKeyFields + @sTAB + ‘@‘ + @sColumnName + ‘ ‘ + @sTypeName IF (@nAlternateType = 2) --decimal, numeric SET @sKeyFields = @sKeyFields + ‘(‘ + CAST(@nColumnPrecision AS varchar(3)) + ‘, ‘ + CAST(@nColumnScale AS varchar(3)) + ‘)‘ ELSE IF (@nAlternateType = 1) --character and binary SET @sKeyFields = @sKeyFields + ‘(‘ + CAST(@nColumnLength AS varchar(4)) + ‘)‘ IF (@sWhereClause = ‘‘) SET @sWhereClause = @sWhereClause + ‘WHERE ‘ ELSE SET @sWhereClause = @sWhereClause + ‘ AND ‘ SET @sWhereClause = @sWhereClause + @sTAB + @sColumnName + ‘ = @‘ + @sColumnName + @sCRLF END IF (@sSelectClause = ‘‘) SET @sSelectClause = @sSelectClause + ‘SELECT‘ ELSE SET @sSelectClause = @sSelectClause + ‘,‘ + @sCRLF SET @sSelectClause = @sSelectClause + @sTAB + @sColumnName FETCH NEXT FROM crKeyFields INTO @sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType, @nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable, @IsIdentity, @sTypeName, @sDefaultValue END CLOSE crKeyFields DEALLOCATE crKeyFields SET @sSelectClause = @sSelectClause + @sCRLF SET @sProcText = @sProcText + @sKeyFields + @sCRLF SET @sProcText = @sProcText + ‘AS‘ + @sCRLF SET @sProcText = @sProcText + @sCRLF SET @sProcText = @sProcText + @sSelectClause SET @sProcText = @sProcText + ‘FROM ‘ + @sTableName + @sCRLF SET @sProcText = @sProcText + @sWhereClause SET @sProcText = @sProcText + @sCRLF IF @bExecute = 0 SET @sProcText = @sProcText + ‘GO‘ + @sCRLF PRINT @sProcText IF @bExecute = 1 EXEC (@sProcText) GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE PROC pr__SYS_MakeUpdateRecordProc @sTableName varchar(128), @bExecute bit = 0 AS IF dbo.fnTableHasPrimaryKey(@sTableName) = 0 BEGIN RAISERROR (‘Procedure cannot be created on a table with no primary key.‘, 10, 1) RETURN END DECLARE @sProcText varchar(8000), @sKeyFields varchar(2000), @sSetClause varchar(2000), @sWhereClause varchar(2000), @sColumnName varchar(128), @nColumnID smallint, @bPrimaryKeyColumn bit, @nAlternateType int, @nColumnLength int, @nColumnPrecision int, @nColumnScale int, @IsNullable bit, @IsIdentity int, @sTypeName varchar(128), @sDefaultValue varchar(4000), @sCRLF char(2), @sTAB char(1) SET @sTAB = char(9) SET @sCRLF = char(13) + char(10) SET @sProcText = ‘‘ SET @sKeyFields = ‘‘ SET @sSetClause = ‘‘ SET @sWhereClause = ‘‘ SET @sProcText = @sProcText + ‘IF EXISTS(SELECT * FROM sysobjects WHERE name = ‘‘prApp_‘ + @sTableName + ‘_Update‘‘)‘ + @sCRLF SET @sProcText = @sProcText + @sTAB + ‘DROP PROC prApp_‘ + @sTableName + ‘_Update‘ + @sCRLF IF @bExecute = 0 SET @sProcText = @sProcText + ‘GO‘ + @sCRLF SET @sProcText = @sProcText + @sCRLF PRINT @sProcText IF @bExecute = 1 EXEC (@sProcText) SET @sProcText = ‘‘ SET @sProcText = @sProcText + ‘----------------------------------------------------------------------------‘ + @sCRLF SET @sProcText = @sProcText + ‘-- Update a single record in ‘ + @sTableName + @sCRLF SET @sProcText = @sProcText + ‘----------------------------------------------------------------------------‘ + @sCRLF SET @sProcText = @sProcText + ‘CREATE PROC prApp_‘ + @sTableName + ‘_Update‘ + @sCRLF DECLARE crKeyFields cursor for SELECT * FROM dbo.fnTableColumnInfo(@sTableName) ORDER BY 2 OPEN crKeyFields FETCH NEXT FROM crKeyFields INTO @sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType, @nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable, @IsIdentity, @sTypeName, @sDefaultValue WHILE (@@FETCH_STATUS = 0) BEGIN IF (@sKeyFields <> ‘‘) SET @sKeyFields = @sKeyFields + ‘,‘ + @sCRLF SET @sKeyFields = @sKeyFields + @sTAB + ‘@‘ + @sColumnName + ‘ ‘ + @sTypeName IF (@nAlternateType = 2) --decimal, numeric SET @sKeyFields = @sKeyFields + ‘(‘ + CAST(@nColumnPrecision AS varchar(3)) + ‘, ‘ + CAST(@nColumnScale AS varchar(3)) + ‘)‘ ELSE IF (@nAlternateType = 1) --character and binary SET @sKeyFields = @sKeyFields + ‘(‘ + CAST(@nColumnLength AS varchar(4)) + ‘)‘ IF (@bPrimaryKeyColumn = 1) BEGIN IF (@sWhereClause = ‘‘) SET @sWhereClause = @sWhereClause + ‘WHERE ‘ ELSE SET @sWhereClause = @sWhereClause + ‘ AND ‘ SET @sWhereClause = @sWhereClause + @sTAB + @sColumnName + ‘ = @‘ + @sColumnName + @sCRLF END ELSE IF (@IsIdentity = 0) BEGIN IF (@sSetClause = ‘‘) SET @sSetClause = @sSetClause + ‘SET‘ ELSE SET @sSetClause = @sSetClause + ‘,‘ + @sCRLF SET @sSetClause = @sSetClause + @sTAB + @sColumnName + ‘ = ‘ IF (@sTypeName = ‘timestamp‘) SET @sSetClause = @sSetClause + ‘NULL‘ ELSE IF (@sDefaultValue IS NOT NULL) SET @sSetClause = @sSetClause + ‘COALESCE(@‘ + @sColumnName + ‘, ‘ + @sDefaultValue + ‘)‘ ELSE SET @sSetClause = @sSetClause + ‘@‘ + @sColumnName END IF (@IsIdentity = 0) BEGIN IF (@IsNullable = 1) OR (@sTypeName = ‘timestamp‘) SET @sKeyFields = @sKeyFields + ‘ = NULL‘ END FETCH NEXT FROM crKeyFields INTO @sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType, @nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable, @IsIdentity, @sTypeName, @sDefaultValue END CLOSE crKeyFields DEALLOCATE crKeyFields SET @sSetClause = @sSetClause + @sCRLF SET @sProcText = @sProcText + @sKeyFields + @sCRLF SET @sProcText = @sProcText + ‘AS‘ + @sCRLF SET @sProcText = @sProcText + @sCRLF SET @sProcText = @sProcText + ‘UPDATE ‘ + @sTableName + @sCRLF SET @sProcText = @sProcText + @sSetClause SET @sProcText = @sProcText + @sWhereClause SET @sProcText = @sProcText + @sCRLF IF @bExecute = 0 SET @sProcText = @sProcText + ‘GO‘ + @sCRLF PRINT @sProcText IF @bExecute = 1 EXEC (@sProcText) GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE FUNCTION dbo.fnCleanDefaultValue(@sDefaultValue varchar(4000)) RETURNS varchar(4000) AS BEGIN RETURN SubString(@sDefaultValue, 2, DataLength(@sDefaultValue)-2) END GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE FUNCTION dbo.fnColumnDefault(@sTableName varchar(128), @sColumnName varchar(128)) RETURNS varchar(4000) AS BEGIN DECLARE @sDefaultValue varchar(4000) SELECT @sDefaultValue = dbo.fnCleanDefaultValue(COLUMN_DEFAULT) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @sTableName AND COLUMN_NAME = @sColumnName RETURN @sDefaultValue END GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE FUNCTION dbo.fnIsColumnPrimaryKey(@sTableName varchar(128), @nColumnName varchar(128)) RETURNS bit AS BEGIN DECLARE @nTableID int, @nIndexID int, @i int SET @nTableID = OBJECT_ID(@sTableName) SELECT @nIndexID = indid FROM sysindexes WHERE id = @nTableID AND indid BETWEEN 1 And 254 AND (status & 2048) = 2048 IF @nIndexID Is Null RETURN 0 IF @nColumnName IN (SELECT sc.[name] FROM sysindexkeys sik INNER JOIN syscolumns sc ON sik.id = sc.id AND sik.colid = sc.colid WHERE sik.id = @nTableID AND sik.indid = @nIndexID) BEGIN RETURN 1 END RETURN 0 END GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE FUNCTION dbo.fnTableColumnInfo(@sTableName varchar(128)) RETURNS TABLE AS RETURN SELECT c.name AS sColumnName, c.colid AS nColumnID, dbo.fnIsColumnPrimaryKey(@sTableName, c.name) AS bPrimaryKeyColumn, CASE WHEN t.name IN (‘char‘, ‘varchar‘, ‘binary‘, ‘varbinary‘, ‘nchar‘, ‘nvarchar‘) THEN 1 WHEN t.name IN (‘decimal‘, ‘numeric‘) THEN 2 ELSE 0 END AS nAlternateType, c.length AS nColumnLength, c.prec AS nColumnPrecision, c.scale AS nColumnScale, c.IsNullable, SIGN(c.status & 128) AS IsIdentity, t.name as sTypeName, dbo.fnColumnDefault(@sTableName, c.name) AS sDefaultValue FROM syscolumns c INNER JOIN systypes t ON c.xtype = t.xtype and c.usertype = t.usertype WHERE c.id = OBJECT_ID(@sTableName) GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE FUNCTION dbo.fnTableHasPrimaryKey(@sTableName varchar(128)) RETURNS bit AS BEGIN DECLARE @nTableID int, @nIndexID int SET @nTableID = OBJECT_ID(@sTableName) SELECT @nIndexID = indid FROM sysindexes WHERE id = @nTableID AND indid BETWEEN 1 And 254 AND (status & 2048) = 2048 IF @nIndexID IS NOT Null RETURN 1 RETURN 0 END GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。