/ Published in: SQL

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),
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
USE master GO IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[sp_ColDefinition]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[sp__ColDefinition] GO IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[fn_SizePrecScale]') AND xtype IN (N'FN', N'IF', N'TF')) DROP FUNCTION [dbo].[fn_SizePrecScale] GO CREATE FUNCTION fn_SizePrecScale( @TYPE VARCHAR(50), @LENGTH INT, @Prec INT, @Scale INT) RETURNS VARCHAR(100) AS BEGIN DECLARE @RC VARCHAR(100) IF @TYPE IN ('smalldatetime','datatime','text','image','bit','ntext','uniqueidentifier','bigint','int','smallint','tinyint','money','smallmoney') SET @RC = @TYPE ELSE IF @TYPE IN('decimal','numeric') SET @RC = @TYPE + '(' + CAST(@LENGTH AS VARCHAR) + ') [' + CAST(@Prec AS VARCHAR) + ',' + CAST(@Scale AS VARCHAR) + ']' ELSE SET @RC = @TYPE + '(' + CAST(@LENGTH AS VARCHAR) + ')' RETURN(@RC) END GO CREATE PROC dbo.sp_ColDefinition @ObjectName sysname AS BEGIN DECLARE @xtype CHAR(2) SELECT @xtype = xtype FROM sysobjects WHERE name = @ObjectName IF @xtype NOT IN('U','FN','V','P') BEGIN RAISERROR ('L''objet n''existe pas dans le catalogue', 16, 1) RETURN END IF @xtype IN ('U','V') SELECT [DECLARE] = '@' + C.name + ' ' + dbo.fn_SizePrecScale(T.name,C.length,C.xprec,C.xscale) + ',', C.name + ',' AS [INSERT INTO], '@' + C.name + ',' AS [VALUES], C.name + ' = @' + C.name + ',' AS [SET], '@' + C.name + ' = ' + C.name + ',' AS [SELECT] FROM sysobjects O, syscolumns C, systypes T WHERE O.id = C.id AND C.xtype = T.xtype AND O.name = @ObjectName AND T.xtype = T.xusertype ORDER BY C.colorder ELSE SELECT [DECLARE] = C.name + ' ' + dbo.fn_SizePrecScale(T.name,C.length,C.xprec,C.xscale) + ',' FROM sysobjects O, syscolumns C, systypes T WHERE O.id = C.id AND C.xtype = T.xtype AND O.name = @ObjectName AND T.xtype = T.xusertype ORDER BY C.colorder END GO
URL: http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=549&lngWId=5
Comments
