首页 > 代码库 > 生成存储过程代码

生成存储过程代码

下面是自动产生存储过程的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
View Code