Thanks Flo and Gail,

but the requirement is

1. to compare two tables

2. in these two tables there are 50 columns

3. 1 or 2 or 3(max) columns are of type NTEXT

4. so excluding those 1 or 2 or 3 columns i need to compare all remaining columns.

the code i'm using for this is:

CODE_BLOCK-1

select 'table1' as tblName, * from

(select * from table1

except

select * from table2) x

union all

select 'table2' as tblName, * from

(select * from table2

except

select *

from table1) x

If I need to write all the columns it will be confusing(rather than confusing it will be difficult)

now I have this code:

CODE_BLOCK-2

DECLARE @excluded_columns TABLE (name SYSNAME)

DECLARE @table_name SYSNAME

DECLARE @columns NVARCHAR(MAX)

SET @table_name = 'csd_temp_1'

-- Excluded columns

INSERT INTO @excluded_columns VALUES ('Recommendations')

INSERT INTO @excluded_columns VALUES ('Lessons Learned')

SET @columns = ''

SELECT @columns = @columns + ', ' + QUOTENAME(name)

FROM sys.columns

WHERE object_id = OBJECT_ID(@table_name)

--missed line

and name NOT IN(select name from @excluded_columns)

SET @columns = RIGHT(@columns, LEN(@columns) - 2)

EXECUTE ('SELECT ' + @columns + ' FROM ' + @table_name)

i'm planning to create a procedure combinig the above 2 code blocks

input parameters will be: table1, table2, col1, col2, .....coln (NTEXT columns)

I need help in this, I do not know how many NTEXT columns will be there,

so the procedure should accept all the input parameters(first 2 being table names and remaining NTEXT columns)

In CODE_BLOCK-2, I need the sproc to insert the input NTEXT columns into @excluded_columns as below

-- Excluded columns

INSERT INTO @excluded_columns VALUES ('col1')

INSERT INTO @excluded_columns VALUES ('col2')

INSERT INTO @excluded_columns VALUES ('col3')

.

INSERT INTO @excluded_columns VALUES ('coln')

I am just trying to make it in general rather than only for a fixed columns.

I will update here whatever I have done ASAP.

Please let me know if this post is confusing, I will try to modify...

Thanks

San