August 19, 2013 at 2:42 am
Colin Davidson (8/19/2013)
HiI thought you would be able to work out the easy part
select ' select softwaremanufacturer,productname,productversion' as c1
union
select ' ,'+c.name as c1
from syscolumns c inner join sysobjects o on c.id = o.id where o.name = 'newtable'
and c.name not in ('softwaremanufacturer','productname','productversion')
union
select 'from newtable' as c1
order by c1
note 2 leading spaces before 1st select - 1 leading space before , and no leading spaces before from - this will put the select in the order you require.
When posting code directly into the text multiple consecutive spaces will be rendered as a single space which means that when the OP used copy/paste to copy your code to SSMS he lost one of the spaces before select.
To avoid this issue you should always use code formatting when posting code. Just click on the IFCode shortcut for code=sql to the left of the input box and paste your code between the start and end tags.
Like this:
select ' select softwaremanufacturer,productname,productversion' as c1
union
select ' ,'+c.name as c1
from syscolumns c inner join sysobjects o on c.id = o.id where o.name = 'newtable'
and c.name not in ('softwaremanufacturer','productname','productversion')
union
select 'from newtable' as c1
order by c1
But my code is still more useful since it returns the sql directly as a string to the code 🙂
August 19, 2013 at 8:36 am
Stefan_G (8/19/2013)
But my code is still more useful since it returns the sql directly as a string to the code 🙂
The attempt is warranted but using XML has its drawbacks. Try running your code where there is a column in the table containing an ampersand, like this:
use tempdb
go
if object_id('dbo.newtable') is not null drop table dbo.newtable
go
create table dbo.newtable (
endeffectivedate int,
starteffectivedate int,
softwaremanufacturer int,
productname int,
productversion INT,
[A & B] VARCHAR(100)
)
go
DECLARE @sql VARCHAR(MAX)
SELECT @sql = 'select softwaremanufacturer,productname,productversion' + (
SELECT ',' + QUOTENAME(name)
FROM sys.columns c
WHERE c.object_id = OBJECT_ID('dbo.newtable')
AND name NOT IN ('softwaremanufacturer', 'productname', 'productversion')
FOR
XML PATH('')
) + ' from dbo.newtable'
SELECT @sql
If you work with report writers or Excel users that are not skilled in developing SQL databases but are tasked with building tables then you'll know this is not an uncommon scenario.
Here is another way to bring it into a single call without using XML:
USE YourDatabaseName;
GO
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'newtable')
AND type IN (N'U') )
DROP TABLE newtable;
GO
CREATE TABLE newtable
(
softwaremanufacturer VARCHAR(100),
productname VARCHAR(100),
productversion VARCHAR(100),
a VARCHAR(100),
b VARCHAR(100),
c VARCHAR(100),
d VARCHAR(100)
);
GO
DECLARE @sql NVARCHAR(MAX) = N'';
WITH cte
AS (
SELECT 'select softwaremanufacturer,productname,productversion' AS piece,
1 AS part
UNION ALL
SELECT ' ,' + c.name,
2 AS part
FROM sys.columns c
INNER JOIN sys.objects o ON c.object_id = o.object_id
WHERE o.name = 'newtable'
AND c.name NOT IN ('softwaremanufacturer', 'productname', 'productversion')
UNION ALL
SELECT ' from newtable',
3 AS part
)
SELECT @sql += piece
FROM cte
ORDER BY part;
EXEC(@sql);
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 19, 2013 at 10:03 pm
And for completeness here is your XML version that handles character entitization properly by using TYPE with FOR XML and the .value XQuery method:
USE YourDatabaseName
go
IF OBJECT_ID('dbo.newtable') IS NOT NULL
DROP TABLE dbo.newtable
go
CREATE TABLE dbo.newtable
(
endeffectivedate INT,
starteffectivedate INT,
softwaremanufacturer INT,
productname INT,
productversion INT,
[A & B] INT
)
go
DECLARE @sql VARCHAR(MAX)
SELECT @sql = 'select softwaremanufacturer,productname,productversion' + (
SELECT ',' + QUOTENAME(name)
FROM sys.columns c
WHERE c.object_id = OBJECT_ID('dbo.newtable')
AND name NOT IN ('softwaremanufacturer', 'productname', 'productversion')
FOR XML PATH(''), TYPE
).value('.[1]', 'NVARCHAR(MAX)') + ' from dbo.newtable'
-- Display the generated statement
SELECT @sql;
-- Execute the generated statement
EXEC(@sql);
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply