Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6

    Answered: Thoughts on predetermining data types

    Code:
    SELECT DISTINCT 
    schemas.name + '.' + all_objects.name AS TableName 		
    --,syscolumns.id AS ColumnId		
    ,syscolumns.name  AS ColumnName
    ,syscolumns.colid AS ColumnID 
    ,CASE WHEN syscomments.TEXT IS NULL THEN '' ELSE syscomments.TEXT END AS ColumnDefault
    --,sys.all_objects.type AS [Type]
    ,sys.all_objects.type_desc As [Type_Desc]
    ,CASE WHEN syscolumns.isnullable = 0 THEN 'nulls not allowed' ELSE 'nulls allowed' END AS NULLSTATUS
    --,syscolumns.isnullable AS IsNullable 		
    ,systypes.name AS DataType
    ,syscolumns.length AS CharacterMaximumLength
    ,CASE WHEN Table_Properties.[value] IS NULL THEN '' ELSE Table_Properties.[value] END AS [Table_Description]
    ,CASE WHEN Column_Properties.[value] IS NULL THEN '' ELSE Column_Properties.[value] END AS [Column_Description] 
              INTO Econdisc_Data_Dictionary
              FROM syscolumns 		
    					INNER JOIN sys.systypes  
    						ON syscolumns.xtype = systypes.xtype 		
    					LEFT JOIN sys.all_objects  
    						ON syscolumns.id = all_objects.[object_id]		
    					LEFT OUTER JOIN sys.extended_properties AS Column_Properties 
    						ON (Column_Properties .minor_id = syscolumns.colid AND Column_Properties.major_id = syscolumns.id)
    					LEFT OUTER JOIN sys.extended_properties AS Table_Properties 
    						ON (Table_Properties .minor_id = 0 AND Table_Properties.major_id = syscolumns.id)
    					LEFT OUTER JOIN sys.syscomments  
    						ON syscolumns.cdefault = syscomments.id 		
    				    LEFT OUTER JOIN sys.schemas 
    						ON schemas.[schema_id] = all_objects.[schema_id]
    
    
    			
    								WHERE syscolumns.id IN (SELECT id                            
    									  FROM   sysobjects                           
    									       WHERE  xtype = 'U' or xtype = 'v')          
                                                     AND (systypes.name <> 'sysname')
                                                         AND (sys.all_objects.is_ms_shipped = '0')
                                                          
    
    ORDER BY   TableName, syscolumns.colid, Type_Desc;
    As you can see I am inserting this into a table. The table is schedule through a job and I am just dropping the table step one and insert this into another table. Well...... The notes fields table and columns is defaulting to variant.

    Does it make sense just truncate the table instead of deleting it. Of course creating the structure and then dropping the information in there. I could set the variant data type to varchar.

    Thoughts on this strategy?

    The reason why I am doing this is the BO environment is flaking out from that data type according to the BO admin.

  2. Best Answer
    Posted by Pat Phelan

    "Yes, truncate the table instead of dropping it. I think that was actually built into my original suggestion (so it only created the table once using a "dud" that included WHERE 0 = 1. That would also allow you to add on (and preserve from run to run) additional columns that were either computed or were only for short term use.

    -PatP"


  3. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Yes, truncate the table instead of dropping it. I think that was actually built into my original suggestion (so it only created the table once using a "dud" that included WHERE 0 = 1. That would also allow you to add on (and preserve from run to run) additional columns that were either computed or were only for short term use.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #3
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Pat ended up manually building out the table, the Case statement was converting to a variant datatype. Once I explicitly invoked NVARCHAR it worked just fine. Thanks for the follow up I appreciate it as always!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •