Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6

    Answered: Assistance with System Query V2

    Code:
    SELECT *
       INTO #myColumns
       FROM INFORMATION_SCHEMA.COLUMNS
       WHERE 0 = 1
    
    INSERT INTO #myColumns
       EXECUTE sp_msforeachdb 'SELECT * FROM [?].INFORMATION_SCHEMA.COLUMNS';
    
    SELECT DISTINCT 
    #myColumns.Table_Catalog as Database_Name    --Comment out if you don't run the stored procedure.
    ,schemas.name AS SchemaName
    ,all_objects.name AS TableName 		
    --,syscolumns.id AS ColumnId		
    ,syscolumns.name  AS ColumnName
    ,syscolumns.colid AS ColumnID 
    ,syscomments.TEXT 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
    ,Table_Properties.[value] AS [Table_Description]
    ,Column_Properties.[value] AS [Column_Description]  ---- Column Description
              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]
    					INNER JOIN #myColumns                                ---Remove to not join to database temp table
    					    ON #myColumns.Table_Name = all_objects.name
    			
    								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')
                                                           AND #myColumns.Table_Catalog <> 'master'
    
    ORDER BY Database_Name, type_desc, TableName, syscolumns.colid;    ---Remove Database_Name if temp table is removed
    
    
    DROP TABLE #myColumns;
    Pat you helped me with this script last time to pull in all the database to the adjourning information.

    Now I am facing an anomoly. The query is returning some of the databases but not all of them. And in some cases it's only pulling back some of the tables in that database not all.

    Any reason why off the top of your head?

  2. Best Answer
    Posted by Pat Phelan

    "That was the clue that I needed! Revised code should be:
    Code:
    EXECUTE sp_msforeachdb '
    SELECT
    ''?'' AS Database_Name
    ,  schemas.name AS SchemaName
    ,  all_objects.name AS TableName 		
    --,syscolumns.id AS ColumnId		
    ,  syscolumns.name  AS ColumnName
    ,  syscolumns.colid AS ColumnID 
    ,  syscomments.TEXT AS ColumnDefault
    ,  all_objects.type AS [Type]
    ,  all_objects.type_desc As [Type_Desc]
    ,  CASE
          WHEN syscolumns.isnullable = 1 THEN ''nulls allowed''
          ELSE ''nulls not allowed''
       END AS NULLSTATUS
    --,syscolumns.isnullable AS IsNullable 		
    ,  systypes.name AS DataType
    ,  syscolumns.length AS CharacterMaximumLength
    ,  Table_Properties.[value] AS [Table_Description]
    ,  Column_Properties.[value] AS [Column_Description]  ---- Column Description
       FROM [?].dbo.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  (all_objects.type = ''U'' or all_objects.type = ''v'')          
          AND (systypes.name <> ''sysname'')
          AND (all_objects.is_ms_shipped = ''0'')
          AND ''?'' <> ''master''
    ORDER BY Database_Name, type_desc, TableName, syscolumns.colid;    ---Remove Database_Name if temp table is removed
    '
    -PatP"


  3. #2
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    I think it has to do with the Join.

    SELECT *
    INTO #myColumns
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE 0 = 1

    INSERT INTO #myColumns
    EXECUTE sp_msforeachdb 'SELECT * FROM [?].INFORMATION_SCHEMA.COLUMNS';

    SELECT * FROM #MYCOLUMNS;

    DROP TABLE #myColumns;

    This returns all the tables, why would the join on all_objects.name omit some of the tables in the databases? I'll keep experimenting.

    I went Left Right and Join on that # table and it still is only pulling back some of the tables in the schema. Does the SP cache memory somehow preventing full results?
    Last edited by VLOOKUP; 06-19-15 at 12:28.

  4. #3
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Code:
    SELECT *
       INTO #myColumns
       FROM INFORMATION_SCHEMA.COLUMNS
       WHERE 0 = 1
    
    INSERT INTO #myColumns
       EXECUTE sp_msforeachdb 'SELECT * FROM [?].INFORMATION_SCHEMA.COLUMNS';
       
      SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
      
       FROM #MYCOLUMNS 
       
       WHERE TABLE_CATALOG NOT IN ('msdb', 'master', 'temp', 'tempdb')
       
       ORDER BY TABLE_CATALOG, TABLE_NAME, ORDINAL_POSITION 
       
       DROP TABLE #myColumns;
    This brings in almost all the information. Now I am need to get the column description and table description tied with the information.

    Thoughts?

  5. #4
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Disregard scraping the project. Just going to focus on the main DW and build a dictionary off of this.

    Thanks

  6. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    The tables you are joining against (sys.all_objects, sys.columns, sys.extended_properties, sys.comments) are database based, so the information you get back from these joins only apply to the local database. Can you get everything you need just from the #MyColumns table? I think everything you are querying for should be there.

  7. #6
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    I wish.....

    Column and Table Description is not joined to that set.

  8. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I will again plug SQL-Spec from http://elsasoft.org because it does everything that you want and more.

    With that said, there are too many moving pieces in this puzzle for me to answer on the first try. You noted that tables seem to be missing from all_objects.
    1. Are the missing objects tables, views, functions, etc?
    2. Does the user running your query have permission to access the missing objects?
    3. Are any database synonyms active in this database?
    4. What Version (2000-2016) of SQL Server produces the missing results?
    5. What Edition (Developer-Enterprise) of SQL Server produces the missing results?
    6. Does DBCC show any errors for this database?

    This question is obscure enough that I may need several rounds of questions to help you find an answer.

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

  9. #8
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    @ Pat

    I'm only filtering for V and U (Tables and Views). However with that filtering only some of the tables are coming from some of the databases. It's a really strange behavior.

    I can update the tables that aren't pulling back from the original query. OBTW the user is me. If I manually change the database in the drop down in SSMS it will reveal all the tables in the query.

    Not that I am aware of

    2008 10.0.1600.22

    Pat we are using an enterprise version I believe. We can set jobs backups etc.

  10. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Just for giggles, go to the database, plug your table name into this script, and run:
    Code:
    SELECT xtype, type, *
       FROM sysobjects AS o
       WHERE  'Your table name goes here' = o.name
    Is the type what you expected? How about the schema and the owner id?

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

  11. #10
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    It shows as a table under xtype and type ('U') and the table name shows correctly in this instance.

    I do see an 'id' but not an owner id unless they are synonymous.

  12. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    That was the clue that I needed! Revised code should be:
    Code:
    EXECUTE sp_msforeachdb '
    SELECT
    ''?'' AS Database_Name
    ,  schemas.name AS SchemaName
    ,  all_objects.name AS TableName 		
    --,syscolumns.id AS ColumnId		
    ,  syscolumns.name  AS ColumnName
    ,  syscolumns.colid AS ColumnID 
    ,  syscomments.TEXT AS ColumnDefault
    ,  all_objects.type AS [Type]
    ,  all_objects.type_desc As [Type_Desc]
    ,  CASE
          WHEN syscolumns.isnullable = 1 THEN ''nulls allowed''
          ELSE ''nulls not allowed''
       END AS NULLSTATUS
    --,syscolumns.isnullable AS IsNullable 		
    ,  systypes.name AS DataType
    ,  syscolumns.length AS CharacterMaximumLength
    ,  Table_Properties.[value] AS [Table_Description]
    ,  Column_Properties.[value] AS [Column_Description]  ---- Column Description
       FROM [?].dbo.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  (all_objects.type = ''U'' or all_objects.type = ''v'')          
          AND (systypes.name <> ''sysname'')
          AND (all_objects.is_ms_shipped = ''0'')
          AND ''?'' <> ''master''
    ORDER BY Database_Name, type_desc, TableName, syscolumns.colid;    ---Remove Database_Name if temp table is removed
    '
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  13. #12
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Pat - I must be missing the bus, but is that code run ready? I changed the ''' to ' but I am getting an parameter error from the SP. Should I be using part of the original code as well?

  14. #13
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Code:
    EXECUTE sp_msforeachdb 'SELECT * FROM [?].INFORMATION_SCHEMA.COLUMNS';
    
    SELECT
       '?' AS Database_Name
    ,  schemas.name AS SchemaName
    ,  all_objects.name AS TableName 		
    --,syscolumns.id AS ColumnId		
    ,  syscolumns.name  AS ColumnName
    ,  syscolumns.colid AS ColumnID 
    ,  syscomments.TEXT AS ColumnDefault
    ,  all_objects.type AS [Type]
    ,  all_objects.type_desc As [Type_Desc]
    ,  CASE
          WHEN syscolumns.isnullable = 1 THEN 'nulls allowed'
          ELSE 'nulls not allowed'
       END AS NULLSTATUS
    --,syscolumns.isnullable AS IsNullable 		
    ,  systypes.name AS DataType
    ,  syscolumns.length AS CharacterMaximumLength
    ,  Table_Properties.[value] AS [Table_Description]
    ,  Column_Properties.[value] AS [Column_Description]  ---- Column Description
       FROM [?].dbo.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  (all_objects.type = 'U' or all_objects.type = 'v')          
          AND (systypes.name <> 'sysname')
          AND (all_objects.is_ms_shipped = '0')
          AND '?' <> 'master'
    ORDER BY Database_Name, type_desc, TableName, syscolumns.colid;    ---Remove Database_Name if temp table is removed;

    Pat this code worked right here. It pulled a different database for each returned query. That is pretty sweet.

  15. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The code I posted in #11 runs "as is" on my machine.

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

  16. #15
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Code:
    Msg 201, Level 16, State 4, Procedure sp_MSforeachdb, Line 0
    Procedure or function 'sp_MSforeachdb' expects parameter '@command1', which was not supplied.
    Msg 208, Level 16, State 1, Line 2
    Invalid object name '?.dbo.syscolumns'.

Posting Permissions

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