How to retrive schema bound with a SELECT statement columns?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi ,
We are developing an application in C# to compare data retrieved by two
SELECT queries. User Input for data comparision is two valid SELECT
statements which returns data.

Before starting the data comparison, i would like to validate the data
type of all the columns which are listed in the select statment are same. For
example i've the following two queries

Query 1: SELECT t1.Col2, t2.Col3, t4.Col5 FROM .......
Query 2: SELECT A.Id, B.Name, C.Age FROM .....

Now i want to verify the selected columns from both the queries are having
same data types and column length before extracting data for comparision. i.e
i want to make sure that data type and column lenght of
t1.Col2 is equal to A.Id
t2.Col3 is equal to B.Name
t4.Col5 is equal to C.Age.

I tried working with DataAdapter.FillSchema but i'm not able to get proper
information requried for validation. Most of the SQL data types are being
mapped to Sytem.String and for data types i'm not able to get exact length.
So i'm not able to differentiate the underlaying datatypes.


please help me in validating the data types and length of colunms which are
selected in the query?

Regards,
Gopi














Can you please tell me how to retrieve schema associated with a query? Here
is the sample query for which i want to know DATATYPE, LENGTH of every
column
which i'm selecting in the query.

SELECT Tbl2.Name, Tbl3.Age, Tbl1.ID, Ttb2.DOB
FROM Table1 Tbl1,
Table2 Tbl2
WHERE Tbl1.Key = Tbl2.Table1Key


Thanks in advance for spending your valuable time to look in to this
problem. I would be very much thankfull to you if you help me to write SQL
script( or C# code) to get the schema bound with the query.

Regards,
Gopinath M
 
C# and VB.Net do not use varchar, char, text, vtext as data types. When you
fill a datatable, the ado.net library translates the SQLDatatypes to string,
int, decimal, bit, byte[] and so on.

To compare native storage types read table definitions for the appropriate
tables.

refer to the stored procedures, sp_help, sp_helpdb, sp_helpIndex


here is an example stored procedure to get you column names and their data
types

Declare @objname nvarchar(776)
Declare @objID int
Declare @dbname sysname
declare @sysobj_type char(2)

set @objname = 'Policies'

select @dbname = parsename(@objname,3)

select @objid = id, @sysobj_type = xtype from sysobjects where id =
object_id(@objname)

select
'Column_name' = name,
'Type' = type_name(xusertype)
from syscolumns where id = @objid and number = 0 order by type, colid
 
Back
Top