Select * but exclude a column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Select * but exclude a column

Author

Topic

pontifikas
Starting Member

32 Posts

Posted - 2004-07-23 : 03:47:26

I'd like to ask if I can select all columns from a database while excluding a certain column?

I mean: I have 100 columns and i want all 99 except the 56th for example.
How can I do this without writing all 99?

I need this for a trigger. I want to copy the entire "inserted" or "deleted" tables to a "logfile" table.Unfortunately they contain a Timestamp column (the one I want to exclude) which I cannot insert on the logfile since it requires NULL.

Thanks a lot mates

Kristen
Test

22859 Posts

Posted - 2004-07-23 : 03:59:10

Simple answer to this which you will get from all the regulars here ...

... never ever use SELECT * for this type of job.

Sorry to be the bearer of bad news!

EDIT: By the by, TEXT won't work either - unless you are using an INSTEAD OF trigger

Kristen

pontifikas
Starting Member

32 Posts

Posted - 2004-07-23 : 04:03:55

I see.So, any other ideas?
Do I have to request explicitly 99 fields?

Kristen
Test

22859 Posts

Posted - 2004-07-23 : 05:41:38

Run this to text output (e.g. in Query Analyser) and cut&paste into your code


SELECT TOP 100 COLUMN_NAME + ', '
FROM 	INFORMATION_SCHEMA.COLUMNS
WHERE	TABLE_NAME = 'MyTable'
	AND DATA_TYPE NOT IN ('timestamp')
ORDER BY ORDINAL_POSITION

Kristen

pontifikas
Starting Member

32 Posts

Posted - 2004-07-23 : 06:09:09

This looks good but does it return only the name of the columns or the values as well?
(I am noob in SQL... :( )

Kristen
Test

22859 Posts

Posted - 2004-07-23 : 06:13:18

Just the column names, with a comma after each one. Just cut&paste it where you have the SELECT * just now.

You don't want be thinking about making your trigger do stuff like this dynamically - it will slow SQL down and lead to deadlocks etc.

Kristen

pontifikas
Starting Member

32 Posts

Posted - 2004-07-23 : 06:31:15

Well...its kinda more complicated.
Actually I want to append the results of this query to 3 more fields and all together put them into
the log file.

So I'd like the trigger to look like this:(Which I tasted and is not correct)

CREATE TRIGGER Insert_ajk_log ON [dbo].[AJK]
AFTER INSERT
AS
INSERT into DB_LOG.[dbo].[AJK_LOG]
select getdate(),'INSERTED','',
( select TOP 33 COLUMN_NAME + ', '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'ajk'
AND DATA_TYPE NOT IN ('timestamp')
ORDER BY ORDINAL_POSITION)

FROM INSERTED

AJK table resides in a different DB(in the same server though) than DB_LOG.AJK_LOG is in DB_LOG.

EDIT:I have to do this on many tables so 100 is not the only col number.

pontifikas
Starting Member

32 Posts

Posted - 2004-07-23 : 07:09:27

OK.I figured ut wat you ment.
I thank you pal

Kristen
Test

22859 Posts

Posted - 2004-07-23 : 08:29:50

If you've got loads of these why don't you write a bit of SQL that generates them automatically? They will be a bit rough&ready, but they will do the job:


SET NOCOUNT ON
DECLARE	@strTableName varchar(256),
	@intRowCount int
SELECT	@intRowCount = 1,	-- Force first loop iteration
	@strTableName = ''
WHILE @intRowCount > 0 AND @strTableName IS NOT NULL
BEGIN
	-- Get next table to process
	SELECT	@strTableName = MIN(TABLE_NAME)
	FROM	INFORMATION_SCHEMA.COLUMNS
	WHERE	TABLE_NAME > @strTableName
	SELECT	@intRowCount = @@ROWCOUNT	-- Did we get anything?
	IF @intRowCount = 0 OR @strTableName IS NULL GOTO BailOut

	-- Header bit
	SELECT	
'CREATE TRIGGER Insert_' + @strTableName + '_log ON [dbo].[' + @strTableName + '] 
AFTER INSERT
AS
INSERT into DB_LOG.[dbo].[' + @strTableName + '_LOG]
select getdate(),''INSERTED'','''', '

	-- List of columns
	SELECT	
		-- "Comment out" any columns of unacceptable data types
		CASE WHEN DATA_TYPE IN ('timestamp') THEN '--' ELSE '' END,
		CHAR(9) 
		-- Comma on all-but the first one
		+ CASE WHEN ORDINAL_POSITION > 1 THEN ',' ELSE ' ' END
		+ '[' + COLUMN_NAME + ']'
	FROM 	INFORMATION_SCHEMA.COLUMNS
	WHERE	TABLE_NAME = @strTableName
	ORDER BY ORDINAL_POSITION

	-- Footer bit
	SELECT	'FROM INSERTED'
		-- This will go "bang" if GO is on a line by itself, so fool QA here:
		+ CHAR(13)+CHAR(10)+'GO'
BailOut:
END	-- Loop
SET NOCOUNT OFF