SQL Server - SQL Server 2005

Hi,

 I would like to know how to write the procdeure to get all the filed names(not the values) in this fomat.

(fieldname)field1 = @field(fieldname)

(fieldname)field2 = @field(fieldname)

and @fieldname1,@fieldname2,......

Please help me out.

Thank you.

Keerthi.Cheluvaraj.

SELECT table_name=sysobjects.name,
         column_name=syscolumns.name,
         datatype=systypes.name,
         length=syscolumns.length
    FROM sysobjects
    JOIN syscolumns ON sysobjects.id = syscolumns.id
    JOIN systypes ON syscolumns.xtype=systypes.xtype
   WHERE sysobjects.xtype='U'
ORDER BY sysobjects.name,syscolumns.colid

refer this link :

http://codesnippets.joyent.com/posts/show/337

Hi,

try the below query...

SELECT '@' + COLUMN_NAME as 'ColName' FROM INFORMATION_SCHEMA.COLUMNS COLUMNS WHERE TABLE_NAME ='table NAme'

SELECT syscolumns.name AS FieldsInTable FROM sysobjects

JOIN syscolumns ON sysobjects.id = syscolumns.id

WHERE sysobjects.name = 'spt_values' //Put your table name here

SELECT sysobjects.name TableName, syscolumns.name ColumnName,

systypes.name + '(' + cast(syscolumns.length/ (Case When systypes.name Like '%Char%'

Then SysColumns.TypeStat Else 1 End) as varchar) + ') ' DateTypeLength,

isNull(syscolumns.collation,'') Collation,

case when syscolumns.isnullable = 1 then 'NULL' else 'NOT NULL' end NUllStatus

from syscolumns

inner join sysobjects

on sysobjects.id = syscolumns.id

inner join systypes

on systypes.xtype = syscolumns.xtype

where

-- If you want Only the Columns with collation set

--syscolumns.collation is not null and

sysobjects.xtype = 'U' and SysColumns.Xtype <> 231 

and sysobjects.name Like '%tablename%'

Order By SysObjects.id desc

Regards

HI,

Thank you very much it works,but how do I get in this format

1st Format

(field1  and @field is fieldname. I need to have@symbol)

field1 = @field

field2 = @field

2nd Format, all the filed names should come in one row separated by comma (,)

@fieldname1,@fieldname2,......

Thank you very much

HI,

Thank you very much it works,but how do I get in this format

1st Format

(field1  and @field is fieldname. I need to have@symbol)

field1 = @field

field2 = @field

2nd Format, all the filed names should come in one row separated by comma (,)

@fieldname1,@fieldname2,......

Thank you very much

HI,

Thank you very much it works,but how do I get in this format

1st Format

(field1  and @field is fieldname. I need to have@symbol)

field1 = @field

field2 = @field

2nd Format, all the filed names should come in one row separated by comma (,)

@fieldname1,@fieldname2,......

Thank you very much

HI,

Thank you very much it works,but how do I get in this format

1st Format

(field1  and @field is fieldname. I need to have@symbol)

field1 = @field

field2 = @field

2nd Format, all the filed names should come in one row separated by comma (,)

@fieldname1,@fieldname2,......

Thank you very much

HI,

Thank you very much it works,but how do I get in this format

1st Format

(field1  and @field is fieldname. I need to have@symbol)

field1 = @field

field2 = @field

2nd Format, all the filed names should come in one row separated by comma (,)

@fieldname1,@fieldname2,......

Thank you very much

HI,

Thank you very much it works,but how do I get in this format

1st Format

(field1  and @field is fieldname. I need to have@symbol)

field1 = @field

field2 = @field

2nd Format, all the filed names should come in one row separated by comma (,)

@fieldname1,@fieldname2,......

Thank you very much

Hi,

try the below one...

DECLARE @column VARCHAR(4000)
 
SELECT
    @column = COALESCE(@column + ',', '') + '@' + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS COLUMNS WHERE TABLE_NAME ='tableName'
 
SELECT Colors =  @column

Vasanth,

Thank you very much its giving me the fields name like in single row. Thank a lot.

But could you help me out for the other format  field1 = @field

Thanks a lot,Thank you very much.

Hi,

what is exact requriement?... should the coulmns like this

field1=@field1,field2=@field2,.....

then here is the code for this

DECLARE @column VARCHAR(4000)
 
SELECT
    @column = COALESCE(@column + ',', '') +  COLUMN_NAME + '=@' + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS COLUMNS WHERE TABLE_NAME ='tableName'
 
SELECT Colors =  @column

Vasanth,

yes exactly but should be one below the other like

field1 = @field

field2 = @field

......

.....

......

Thank you.

Hi,

do you require two formats sepertely, find the query for this...

format1:

select COLUMN_NAME + '=@' + COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS COLUMNS WHERE TABLE_NAME ='tableName'

Format2:

DECLARE @column VARCHAR(4000)
 
SELECT
    @column = COALESCE(@column + ',', '') + '@' + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS COLUMNS WHERE TABLE_NAME ='tableName'
 
SELECT Colors =  @column

Vasanth,

Thank you very much.

You helped me a lot. I never forget your help.

Can I know you?

Thank you very much Vasanth,May God Bless you.

Hi,

thanks... inacse of any issue, you can get a lot of help from this forum... you can post here...

this is my mail id...

vasanth.kumar.d@gmail.com

Hi Vasanth,

Could you please explain me how it works?

Do anybody knows how to doccument a stored procedure? I need to document this.

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

ALTER proc [dbo].[MasUpdate]

@overrideFlag bit = 0 ,-- to override incomplete loads.

@ErrorMessage varchar(500) = 'OK' output

as

declare @rwCount int , @runid int

declare @errorCode int, @rc int

declare @dbname varchar(100), @hostname varchar(100),@errorProc varchar(100)

select @hostname=@@SERVERNAME,@dbname=db_name(),@errorProc='MASUpdate'

begin try

if exists ( select 1 from MM_UpdateMaster where runflag = 'n') and (@overrideFlag = 0)

begin

RAISERROR(60000,16,1,@hostname,@dbname,@errorProc)

end

-- setup run

insert into MM_UpdateMaster (runFlag) values ('n')

set @runid = @@identity

-- Upload entity

execute @RC = MasEntityCsr @vRunId= @runid -- default @vLogging = 3 for all records

-- Upload vehicle

execute @RC = MasVehicleCsr @vRunId= @runid -- default @vLogging = 3 for all records

-- Upload vehicleIvu

execute @RC = MasVehicleIVUCsr @vRunId= @runid -- default @vLogging = 3 for all records

-- Upload IAC

execute @RC = MasIacCsr @vRunId= @runid -- default @vLogging = 3 for all records

-- run complete ....

update MM_UpdateMaster set runFlag = 'y' where runid=@runid

end try

begin catch

set @errorCode = @@error

set @ErrorMessage = error_message()

return @errorCode

end catch

return 1

Could you please explain me how document this procedure..its very urgent please....

like exactly how we get when we type sp_ in SQl help..Iam very much confused with brackets to use like { [

Please help me out .....

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

ALTER proc [dbo].[MasUpdate]

@overrideFlag bit = 0 ,-- to override incomplete loads.

@ErrorMessage varchar(500) = 'OK' output

as

declare @rwCount int , @runid int

declare @errorCode int, @rc int

declare @dbname varchar(100), @hostname varchar(100),@errorProc varchar(100)

select @hostname=@@SERVERNAME,@dbname=db_name(),@errorProc='MASUpdate'

begin try

if exists ( select 1 from MM_UpdateMaster where runflag = 'n') and (@overrideFlag = 0)

begin

RAISERROR(60000,16,1,@hostname,@dbname,@errorProc)

end

-- setup run

insert into MM_UpdateMaster (runFlag) values ('n')

set @runid = @@identity

-- Upload entity

execute @RC = MasEntityCsr @vRunId= @runid -- default @vLogging = 3 for all records

-- Upload vehicle

execute @RC = MasVehicleCsr @vRunId= @runid -- default @vLogging = 3 for all records

-- Upload vehicleIvu

execute @RC = MasVehicleIVUCsr @vRunId= @runid -- default @vLogging = 3 for all records

-- Upload IAC

execute @RC = MasIacCsr @vRunId= @runid -- default @vLogging = 3 for all records

-- run complete ....

update MM_UpdateMaster set runFlag = 'y' where runid=@runid

end try

begin catch

set @errorCode = @@error

set @ErrorMessage = error_message()

return @errorCode

end catch

return 1

Could you please explain me how document this procedure..its very urgent please....

like exactly how we get when we type sp_ in SQl help..Iam very much confused with brackets to use like { [

Please help me out .....

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

ALTER proc [dbo].[MasUpdate]

@overrideFlag bit = 0 ,-- to override incomplete loads.

@ErrorMessage varchar(500) = 'OK' output

as

declare @rwCount int , @runid int

declare @errorCode int, @rc int

declare @dbname varchar(100), @hostname varchar(100),@errorProc varchar(100)

select @hostname=@@SERVERNAME,@dbname=db_name(),@errorProc='MASUpdate'

begin try

if exists ( select 1 from MM_UpdateMaster where runflag = 'n') and (@overrideFlag = 0)

begin

RAISERROR(60000,16,1,@hostname,@dbname,@errorProc)

end

-- setup run

insert into MM_UpdateMaster (runFlag) values ('n')

set @runid = @@identity

-- Upload entity

execute @RC = MasEntityCsr @vRunId= @runid -- default @vLogging = 3 for all records

-- Upload vehicle

execute @RC = MasVehicleCsr @vRunId= @runid -- default @vLogging = 3 for all records

-- Upload vehicleIvu

execute @RC = MasVehicleIVUCsr @vRunId= @runid -- default @vLogging = 3 for all records

-- Upload IAC

execute @RC = MasIacCsr @vRunId= @runid -- default @vLogging = 3 for all records

-- run complete ....

update MM_UpdateMaster set runFlag = 'y' where runid=@runid

end try

begin catch

set @errorCode = @@error

set @ErrorMessage = error_message()

return @errorCode

end catch

return 1