April 30, 2009 at 9:34 am
I've taken some code from here and modified it slightly so I now have a function where I supply it a table name and an alias - it then gives me a comma delimited string of all fields, prefixed with the alias string I specify.
This works great, however, rather than having something like:
a.ID, a.Name, a.Type
I would prefer each field on a new line:
a.ID,
a.Name,
a.Type
I tried adding a CHAR(10) and CHAR(13) to the end of my string and it didn't work.
Here's my function, any input appreciated!
CREATE FUNCTION [dbo].[F_Table_Fields_Aliased](@Table varchar(200), @Prefix varchar(20))
RETURNS VARCHAR(MAX) AS
BEGIN
DECLARE @Output VARCHAR(max)
SET @OutPut = ''
SELECT @OutPut = @Output + @Prefix+syscolumns.name + ', '
FROM syscolumns
INNER JOIN sysobjects
ON syscolumns.id = sysobjects.id
INNER JOIN systypes
ON syscolumns.xtype = systypes.xtype
WHERE sysobjects.name = @Table
ORDER BY colorder ASC
Return @Output
END
April 30, 2009 at 9:44 am
I tried this, after setting the query output to Text ("Results to Text" instead of "Results to Grid"):
ALTER FUNCTION [dbo].[F_Table_Fields_Aliased](@Table varchar(200), @Prefix varchar(20))
RETURNS VARCHAR(MAX) AS
BEGIN
DECLARE @Output VARCHAR(max)
SET @OutPut = ''
SELECT @OutPut = @Output + @Prefix + '.' +syscolumns.name + ',
'
FROM syscolumns
INNER JOIN sysobjects
ON syscolumns.id = sysobjects.id
INNER JOIN systypes
ON syscolumns.xtype = systypes.xtype
WHERE sysobjects.name = @Table
ORDER BY colorder ASC
Return @Output
END
GO
select dbo.F_Table_Fields_Aliased('Case_Financial','CF');
The table is one I was using for someone on this site yesterday, so it doesn't matter.
Output was:
-------------------------------------------------------------------------------------------------
CF.cubs_case_financial_guid,
CF.cubs_case_id,
CF.k_claim_amt,
CF.orig_claim_amt,
CF.arrears_calc_from_dt,
CF.arrears_calc_to_dt,
CF.arrears_contract_balance,
CF.arrears_dt_payout_projected,
CF.initial_balance_amt,
CF.initial_interest_amt
(1 row(s) affected)
That appears to be what you're looking for.
I added a period between the prefix and the column name, and a return to the end of the string. You will end up with a comma at the end of the list with this function, which might break what you're trying to do.
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 30, 2009 at 9:53 am
Thanks GSquared.
In my original function I tried adding the CHAR(10) or CHAR(13) to the end of the string (i.e. after the comma) and set results to text - it worked fine BUT it chopped the output off short... the table has something silly like 190 columns in it and it returned only 12 of these, with the 12th being chopped- the sum of these characters is 245, odd!
I thought being a varchar(max) it would display everything.
I tried your function too and it gave an identical results set and problem :/
April 30, 2009 at 9:55 am
You need to change a setting in Management Studio. In Tools, Options, Query Results, SQL Server, Results to Text, the default setting is 256 characters per column. Increase that number and you'll get more of your results being displayed.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 30, 2009 at 10:03 am
Nice - works perfectly! - Thanks GSquared (again!)
This little thing should save me quite a bit of time, pedantic as it may seem!
April 30, 2009 at 10:21 am
Hi
Sorry for barging in 😉
What about selecting the columns instead of print them?
DECLARE @Prefix VARCHAR(10)
DECLARE @Table VARCHAR(128)
SELECT @Prefix = 'pfx.', @Table = 'Calendar'
SELECT @Prefix+syscolumns.name + ', '
FROM syscolumns
INNER JOIN sysobjects
ON syscolumns.id = sysobjects.id
INNER JOIN systypes
ON syscolumns.xtype = systypes.xtype
WHERE sysobjects.name = @Table
ORDER BY colorder
Greets
Flo
May 5, 2009 at 2:45 am
Thanks Flo, just built that into a little stored procedure - should come in handy 🙂
May 5, 2009 at 2:58 am
hi,
also try this
create table #temp
(
slno int identity(1,1),
name1 varchar(100)
)
declare @abc varchar(1000)/*Alwayes should be in max value*/
select @abc = 'a.ID, a.Name, a.Type'
select @abc = 'select ''' + replace (@ABC,',',''' union select ''')+''''
insert into #temp (name1)
exec (@ABC)
select * from #temp
ARUN SAS
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply