Advanced Search

sp_ColDefinition or writing upsert SP in a snap

/ Published in: SQL
Save to your folder(s)
sp_ColDefinition will use the metadata tables to create a table result that looks like this :

DECLARE
---------------------
@title_id varchar(6),
@title varchar(80),
@type char(12),
@pub_id char(4),
@price money,
@advance money,
@royalty int,
@ytd_sales int,
@notes varchar(200),
@pubdate datetime(8),

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. USE master
  2. GO
  3.  
  4. IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[sp_ColDefinition]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
  5. 	DROP PROCEDURE [dbo].[sp__ColDefinition]
  6. GO
  7. IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[fn_SizePrecScale]') AND xtype IN (N'FN', N'IF', N'TF'))
  8. 	DROP FUNCTION [dbo].[fn_SizePrecScale]
  9. GO
  10.  
  11. CREATE FUNCTION fn_SizePrecScale(
  12. 	@TYPE VARCHAR(50),
  13. 	@LENGTH INT,
  14. 	@Prec INT,
  15. 	@Scale INT)
  16. 	RETURNS VARCHAR(100)
  17. AS
  18. BEGIN
  19. 	DECLARE @RC VARCHAR(100)
  20.  
  21. 	IF @TYPE IN ('smalldatetime','datatime','text','image','bit','ntext','uniqueidentifier','bigint','int','smallint','tinyint','money','smallmoney')
  22. 		SET @RC = @TYPE
  23. 	ELSE IF @TYPE IN('decimal','numeric')
  24. 			SET @RC = @TYPE + '(' + CAST(@LENGTH AS VARCHAR) + ') [' + CAST(@Prec AS VARCHAR) + ',' + CAST(@Scale AS VARCHAR) + ']'
  25. 	ELSE
  26. 		SET @RC = @TYPE + '(' + CAST(@LENGTH AS VARCHAR) + ')'
  27.  
  28. 	RETURN(@RC)
  29. END
  30.  
  31. GO
  32. CREATE PROC dbo.sp_ColDefinition
  33. 	@ObjectName sysname
  34. AS
  35. BEGIN
  36. 	DECLARE @xtype CHAR(2)
  37.  
  38. 	SELECT @xtype = xtype FROM sysobjects WHERE name = @ObjectName
  39.  
  40. 	IF @xtype NOT IN('U','FN','V','P')
  41. 	BEGIN
  42. 		RAISERROR ('L''objet n''existe pas dans le catalogue', 16, 1)
  43. 		RETURN
  44. 	END
  45.  
  46. 	IF @xtype IN ('U','V')
  47. 		SELECT [DECLARE] = '@' + C.name + ' ' + dbo.fn_SizePrecScale(T.name,C.length,C.xprec,C.xscale) + ',',
  48. 			C.name + ',' AS [INSERT INTO],
  49. 			'@' + C.name + ',' AS [VALUES],
  50. 			C.name + ' = @' + C.name + ',' AS [SET],
  51. 			'@' + C.name + ' = ' + C.name + ',' AS [SELECT]
  52. 		FROM sysobjects O, syscolumns C, systypes T
  53. 		WHERE O.id = C.id AND C.xtype = T.xtype
  54. 			AND O.name = @ObjectName AND T.xtype = T.xusertype
  55. 		ORDER BY C.colorder
  56. 	ELSE
  57. 		SELECT [DECLARE] = C.name + ' ' + dbo.fn_SizePrecScale(T.name,C.length,C.xprec,C.xscale) + ','
  58. 		FROM sysobjects O, syscolumns C, systypes T
  59. 		WHERE O.id = C.id AND C.xtype = T.xtype
  60. 			AND O.name = @ObjectName AND T.xtype = T.xusertype
  61. 		ORDER BY C.colorder
  62. END
  63. GO

URL: http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=549&lngWId=5

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.