`
lovnet
  • 浏览: 6723724 次
  • 性别: Icon_minigender_1
  • 来自: 武汉
文章分类
社区版块
存档分类
最新评论

多层开发中，经常写SQL SERVER存储过程的朋友看过来!

SQL ServerSQLGoCC++ 
阅读更多

下面是自动产生存储过程的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*
FROMdbo.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*
FROMdbo.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*
FROMdbo.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*
FROMdbo.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)
FROMINFORMATION_SCHEMA.COLUMNS
WHERETABLE_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
SELECTc.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
FROMsyscolumns c
INNER JOIN systypes t ON c.xtype = t.xtype and c.usertype = t.usertype
WHEREc.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

分享到：
| 创建可变大小的CDialog Bar
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    Microsoft SQL Server 2005开发指南

    书中的一些示例和提示详细介绍了如何将SQL Server 2005作为一个多层的数据库平台与Visual Studio 2005一起使用，如何使用SQL CMD创建自定义的管理脚本，以及如何使用SQL Profiler提高性能。. 本书主要内容： ·使用 ...

    ASP.NET2.0+SQLServer2005构建多层应用

    SQLServer2005使用aprilctp版本）来构建一个简单的多层应用的例子，并且说明其中在.NET2.0和SQLServer2005中的一些新的特性，比如objectdatasource,masterpages,clr存储过程，tableadapter向导等。　多

    LTP.Net代码自动生成器(DbToCode)

    DbToCode 同时提供方便的数据库查询管理，SQL脚本生成，存储过程生成，数据库文档生成，Web项目文件发布，代码生成自动导出文件等多项开发工作中常用到的功能，您可以很方便地进行项目开发。 详细功能说明： 1....

    SQLserver-数据库-安全监控系统.doc

    【论文关键词】SQLserver 数据库 安全监控系统 【论文摘要】数据库监控信息获取策略的研究内容包括：数据库威胁来源、威胁特征、 数据库审计事件、数据库运行性能指标等。通过对数据库所受威胁的研究，建立数据库 ...

    易点内容管理系统 DianCMS v6.4.0 SQL版.zip

    0技术，采用Microsoft Access/SQL Server 2000/2005/2008存储过程进行多层架构开发的内容管理系统。其功能设计主要面向中大型企业、各个行业、事业单位以及政府机关等复杂功能站点。系统已建立文章系统、图片系统、...

    asp.net知识库

    将数据库表中的数据生成Insert脚本的存储过程!!! 2分法-通用存储过程分页(top max模式)版本(性能相对之前的not in版本极大提高) 分页存储过程：排序反转分页法 优化后的通用分页存储过程 sql语句 一些Select检索高级...

    易点内容管理系统 DianCMS v6.4.0 SQL版

    0技术，采用Microsoft Access/SQL Server 2000/2005存储过程进行多层架构开发的内容管理系统。其功能设计主要面向中大型企业、各个行业、事业单位以及政府机关等复杂功能站点。系统已建立文章系统、图片系统、下载...

    麦卡(C# 2.0)代码生成器

    麦卡生成的代码基于面向对象的思想和多层架构设计，采用 公共层 + 存储过程 + 实体层 + 数据层 + 逻辑层 + 表示层 的设计，主要实现在 C# 中对应数据库中表的基类代码的自动生成，包括生成属性、添加、修改、删除、...

    Toad 使用快速入门

    在Oracle应用程序的开发过程中，访问数据库对象和编写SQL程序是一件乏味且耗费时间的工作，对数据库进行日常管理也是需要很多SQL脚本才能完成的。Quest Software为此提供了高效的Oracle应用开发工具-Toad（Tools of ...

    DianCMS.SQL_6.4.0.7z

    0技术，采用Access/SQL Server 2005/2008/2012存储过程进行多层架构开发的内容管理系统，其功能设计主要面向中大型企业、各个行业、事业单位以及政府机关等复杂功能站点。系统已建立文章系统、图片系统、下载系统、...

    易点内容管理系统(DianCMS) 6.4.0 SQL.rar

    0技术，采用Access/SQL Server 2005/2008/2012存储过程进行多层架构开发的内容管理系统，其功能设计主要面向中大型企业、各个行业、事业单位以及政府机关等复杂功能站点。系统已建立文章系统、图片系统、下载系统、...

    Codematic2

    Codematic 同时提供方便的多类型数据库管理，查询分析器，SQL脚本生成，存储过程生成，数据库文档生成，Web项目发布，代码批量自动输出等多项开发工作中常用到的功能，您可以很方便轻松地进行项目开发。 详细功能...

    codematic2

    Codematic 同时提供方便的多类型数据库管理，查询分析器，SQL脚本生成，存储过程生成，数据库文档生成，Web项目发布，代码批量自动输出等多项开发工作中常用到的功能，您可以很方便轻松地进行项目开发。 详细功能...

    西部数字代码自动生成系统 v2.0

    减轻开发人员负担，减少代码的键入量，根据sql server数据库表自动生成 BLL,DAL结构的C#代码。包括常用的CRUD(针对数据库表的添加、修改、删除、查询）操作。 支持跨数据库表的访问。 支持数据缓存。 概述 这里所...

    SqlHelper源码及使用实例

    SqlHelper 类中的专用函数主要用于提供一种一致的方式，以便向 SQL Server 数据库发出命令，而不考虑客户端应用程序调用的重载方法实现。SqlHelper 类中的专用实用程序函数包括： SqlHelper是非常方便的数据库操作...

    动软生成器

    Codematic 同时提供方便的多类型数据库管理，查询分析器，SQL脚本生成，存储过程生成，数据库文档生成，Web项目发布，代码批量自动输出等多项开发工作中常用到的功能，您可以很方便轻松地进行项目开发。...

    易点内容管理系统 DianCMS v5.3.0 SQL

    0技术，采用Microsoft Access/SQL Server 2000/2005/2008存储过程进行多层架构开发的内容管理系统。其功能设计主要面向中大型企业、各个行业、事业单位以及政府机关等复杂功能站点。系统已建立文章系统、图片系统、...

    易点内容管理系统（DianCMS）

    0技术，采用Microsoft Access/SQL Server 2000/2005存储过程进行多层架构开发的内容管理系统。其功能设计主要面向中大型企业、各个行业、事业单位以及政府机关等复杂功能站点。系统已建立文章系统、图片系统、下载...

    Net代码生成器 2.16

    &lt;br&gt;　同时提供方便的多类型数据库管理，查询分析器，SQL脚本生成，存储过程生成，数据库文档生成，Web项目发布，代码批量自动输出等多项开发工作中常用到的功能，您可以很方便轻松地进行项目开发。 &lt;br&gt;　须...

Magicbox
Global site tag (gtag.js) - Google Analytics