January 16, 2003 at 5:25 am
Im looking for any useful scripts to aid development. For example when I write a stored procedure to do an insert into a table I need a list of columns names for the table laid out in column order with a comma after each field name. I have a basic scripts to achieve this:--
declare @name varchar(255)
set @name = 'tblDataProvider'
Select
' ' +
name +
','
from
(select
c.name,
ORDINAL_POSITION = convert(int,
(
select count(*)
from syscolumns sc
where sc.id = c.id
AND sc.number = c.number
AND sc.colid <= c.colid
))
from
syscolumns c,
sysobjects o
where
c.id = o.id and
o.name like @name ) as cols
order by
ORDINAL_POSITION
Could someone point me to an online library of these routines please.
Thanks
Chris
January 16, 2003 at 6:31 am
If it is columnnames you want then check Query Analyzer for SQL2K.
Right-click on a table (or stored proc) in objectbrowser. This brings up a menu from where you can create INSERT/UPDATE/DELETE/SELECT statements for tables/views and EXECUTE statement for procedures.
January 16, 2003 at 6:48 am
Thats very useful.
Thanks
January 16, 2003 at 6:50 am
Chris, I think you made this script far more complicated then it needs to be.
Here are two possible examples to do the same thing:
-- example one
set nocount on
select column_name + ',' from information_schema.columns where table_name = 'LHJ_Hardware_PDA'
order by ordinal_position
-- example two
declare @S varchar(8000)
set @S = ''
select @S=@s + case when @S <> '' then ',' else '' end + rtrim(column_name)
from information_schema.columns
where table_name = 'LHJ_Hardware_PDA'
group by table_name, ordinal_position, column_name
print @S
You might want to look at the information_schema stuff in BOL for other usable views.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
January 16, 2003 at 6:53 am
You might want to change 'LHJ_Hardware_PDA' to 'tblDataProvider' in my example.
I hate when I do that.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
January 24, 2003 at 8:32 am
declare @tablename varchar (128)
select @tablename = 'TableName'
declare @tableid int ,@columnpointer int ,
@colcount int ,@columnname varchar (30) ,
@columntype int ,@part1 varchar (7500) ,
@part2 varchar (7500) ,@isidentfieldexist int
SET NOCOUNT ON
create table #tmpcolumndesc
(
internal_id int identity,
columnname varchar(30),
fieldtype smallint
)
select @tableid = id from sysobjects where name = @tablename
if @@rowcount = 0
begin
raiserror 70000 'Invalid Table Name'
return
end
-- ignore timestamp fields
insert into #tmpcolumndesc (columnname,fieldtype)
select
name, xtype
from
syscolumns
where
id = @tableid and xtype <> 189
select @part1 = '('
select @columnpointer = MIN (internal_id),
@colcount = MAX (internal_id)
from
#tmpcolumndesc
while @columnpointer <= @colcount
begin
select @columnname = columnname,
@columntype = fieldtype
from
#tmpcolumndesc
where
internal_id = @columnpointer
if (@@rowcount <> 0)
begin
if (@columnpointer < @colcount)
begin
select @part1 = @part1 + @columnname + ','
end
else
begin
select @part1 = @part1 + @columnname + ')'
end
end
select @columnpointer = @columnpointer + 1
end
drop table #tmpcolumndesc
select @part1
SET NOCOUNT OFF
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply