Help me with constructing the SQL statement please ?

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  December 15, 2015 at 3:18 pm

  #307105

  sp_help 'TestTierMedVisit_Stage1'

  sp_help 'Visit_IN'

  The above 2 tables have identical columns ( The columns names are identical )

  However some of the varchar type fields may have different lengths.

  Can we write a query that shows the following cols ?

  Col_Name, Col_type, Size_on_Tab1, Size_on_Tab2

  So the result set would look like

  ID, VARCHAR, 10, 10

  ADDR , VARCHAR, 20, 15

  Thanks in Advance

• Jacob Wilkins

  One Orange Chip

  Points: 27976

  More actions

  December 15, 2015 at 3:49 pm

  #1846220

  Something like this?

  CREATE TABLE test_table1 (first_string varchar(MAX),

  second_string varchar(48),

  id INT);

  CREATE TABLE test_table2 (first_string varchar(25),

  second_string varchar(58));

  SELECT column_name=c.name,

  data_type=t.name,

  size_on_table_1=MAX(CASE WHEN o.name='test_table1'

  THEN c.max_length

  ELSE NULL END),

  size_on_table_2=MAX(CASE WHEN o.name='test_table2'

  THEN c.max_length

  ELSE NULL END)

  FROM sys.columns c

  INNER JOIN sys.objects o ON c.object_id=o.object_id

  INNER JOIN sys.types t ON c.user_type_id=t.user_type_id

  WHERE o.name IN ('test_table1','test_table2')

  GROUP BY c.name,t.name;

  DROP TABLE test_table1, test_table2;

  Cheers!