Posts
463
Comments
322
Trackbacks
1
Column Definition Stored Procedure

From: http://pscode.com/vb/scripts/ShowCode.asp?txtCodeId=549&lngWId=5

This can help you avoid a lot of typing.  It lists out standard “Insert Into“, “Values“, etc. text when you pass in a table name, like 'exec sp__ColDefinition MyTable'.

---------------------------------------- -- ---------------------------------------- --------------------

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(
varchar(50),
int,
int,
int)
returns varchar(100)
as
begin
DECLARE @RC varchar(100)

IF in ('smalldatetime','datatime','text','image','bit','ntext','uniqueidentifier','bigint','int','smallint','tinyint','money','smallmoney')
SET @RC =
ELSE IF in('decimal','numeric')
SET @RC = + '(' + cast( as varchar) + ') [' + cast( as varchar) + ',' + cast( as varchar) + ']'
ELSE
SET @RC = + '(' + cast( as varchar) + ')'

RETURN
end

GO
CREATE PROC dbo.sp__ColDefinition
sysname
AS
BEGIN
DECLARE char(2)

SELECT = xtype FROM sysobjects WHERE name =

IF not in('U','FN','V','P')
begin
RAISERROR ('L''objet n''existe pas dans le catalogue', 16, 1)
RETURN
end


IF 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 = 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 = and T.xtype = T.xusertype
order by C.colorder
END

posted on Friday, July 08, 2005 1:55 PM
Comments
No comments posted yet.

Post Comment

Title *
Name *
Email
Url
Comment *  
Please add 3 and 7 and type the answer here: