自动产生存储过程的sql 脚本

http://blog.csdn.net/lihonggen0/archive/2004/08/15/75447.aspx

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