June 27, 2016 at 3:40 am
Hi,
I have a table A with sparse columns and columnset column for sparse columns, I have come across situation where I need to compare all the columns in A are there are not with respect to few other tables, So while I am trying to use sys.columns,sys.tables to get the columns from Table A, the Sys columns query only displaying only two columns, one is primary key column and other one is columnset column, where I am trying to look all the columns in Table A. Could any one give some suggestions on this please.
Thank you
June 27, 2016 at 6:35 am
Quick suggestion
😎
SELECT
SAC.object_id
,SAC.name
,SAC.column_id
,SAC.system_type_id
,SAC.user_type_id
,SAC.max_length
,SAC.precision
,SAC.scale
,SAC.collation_name
,SAC.is_nullable
,SAC.is_ansi_padded
,SAC.is_rowguidcol
,SAC.is_identity
,SAC.is_computed
,SAC.is_filestream
,SAC.is_replicated
,SAC.is_non_sql_subscribed
,SAC.is_merge_published
,SAC.is_dts_replicated
,SAC.is_xml_document
,SAC.xml_collection_id
,SAC.default_object_id
,SAC.rule_object_id
,SAC.is_sparse
,SAC.is_column_set
FROM sys.all_columns SAC
WHERE SAC.object_id = OBJECT_ID('TableName')
To compare the values you can concatenate the column values and use the hashbyte to generate a checksum
SELECT
ST.ID_KEY
,HASHBYTES('SHA1',ST.ColSet.value('.','nvarchar(max)')) AS SHA1_VAL
,ROW_NUMBER() OVER
(
PARTITION BY HASHBYTES('SHA1',ST.ColSet.value('.','nvarchar(max)'))
ORDER BY ST.ID_KEY
) AS DUPE_RID
FROM dbo.SomeTable ST
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply