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

    Answered: Assistance with System Query

    Here is what I have so far. I am trying to tie in Database to Schema, TableName, ColumnName, ColumnID, Type etc. Listed below is the query.

    Code:
    SELECT  
    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]
    			
    								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 type_desc, TableName, syscolumns.colid

    The table I am trying to join to is:

    Code:
    SELECT *
    FROM SYS.Databases
    However, I don't seem to be able to find a way to join database to the system tables I have joined to the query. Thoughts? Thanks!

  2. Best Answer
    Posted by Pat Phelan

    "
    Code:
    SELECT *
       INTO #myColumns
       FROM INFORMATION_SCHEMA.COLUMNS
       WHERE 0 = 1
    
    INSERT INTO #myColumns
       EXECUTE sp_msforeachdb 'SELECT * FROM [?].INFORMATION_SCHEMA.COLUMNS'
    
    SELECT *
       FROM #myColumns
    -PatP"


  3. #2
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    I'm wondering if I should look into using this SP? sp_msforeachdb Anyone have any experiencing using this SP?

    Thanks
    V

  4. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    You will probably end up using sp_msforeachdb, depending on your requirement. What are you looking to do? Build a data dictionary? Your final script will look something like
    Code:
    exec sp_msforeachdb 'use ?; insert into dbname.dbo.table (rest of your script here)'
    There's no way to link sys.databases directly to say sys.tables in a particular database, because when you restore a database, you could end up with all sorts of different mappings.

  5. #4
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    MCrowley thanks for the follow up.

    I found a way I think may work. Take a look and let me know what you think if you wouldn't mind. Always great getting another set of eyes on this.

    Code:
    SELECT 
     sys.databases.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
    ,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  sys.databases 
                        INNER JOIN INFORMATION_SCHEMA.COLUMNS
                            ON INFORMATION_SCHEMA.COLUMNS.TABLE_CATALOG = sys.databases.name
                        INNER JOIN  syscolumns 
                            ON SYSCOLUMNS.name = INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME and syscolumns.colid = INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION      		
    					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')
                                                        
                                               
                                               GROUP BY  sys.databases.name
    													,schemas.name 
    													,all_objects.name 		
    													,syscolumns.name  
    													,syscolumns.colid 
    													,syscomments.TEXT
    													,sys.all_objects.type 
    													,sys.all_objects.type_desc 
    													,syscolumns.isnullable 
    													,systypes.name 
    													,syscolumns.length 
    													,Table_Properties.[value] 
    													,Column_Properties.[value] 
                                                        
                                               ORDER BY type_desc, TableName, syscolumns.colid

  6. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    If you are only looking to get the name of the database included in the query, you could always just call the DB_NAME() function. I had thought you were looking to get all tables from all databases in one call.

  7. #6
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    MCrowley I am looking for it to do that actually. Can you use a cursor or some other advanced technique to loop through a variable to return one set?

    I thought it would pull everything in if I did a left outer join on the sys.database table thinking it would pull in all the databases and from there list all the schemas, tables, columns etc. I thought wrong huh....

    If you SELECT * SYS.DATABASES it pulls all of database (name), but if you join out with a left outer it doesn't pull. Weird

    MCrowley - yes trying to build a data dictionary. I want to migrate this into our BO Universe and keep it dynamic so i don't have to mess with it anymore lol
    Last edited by VLOOKUP; 04-17-15 at 15:42.

  8. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Just curious, but have you checked out the INFORMATION_SCHEMA.COLUMNS view that is provided since SQL 2005?

    -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 like that view, it really pulls in a lot of objects. I'm trying to reach multiple databases though, that's why I was drawn to the sysdatabases view, hoping if I joined off of that it would pull in all the schemas etc. I have all the back end joins set on the query to tie the table and column descriptions in, which was a little bit of a bitch.

    Now I'm hosed up on going across multiple databases on one server.

  10. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    SELECT *
       INTO #myColumns
       FROM INFORMATION_SCHEMA.COLUMNS
       WHERE 0 = 1
    
    INSERT INTO #myColumns
       EXECUTE sp_msforeachdb 'SELECT * FROM [?].INFORMATION_SCHEMA.COLUMNS'
    
    SELECT *
       FROM #myColumns
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  11. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Note that you can't presume the structure of the INFORMATION_SCHEMA.COLUMN view. It changes on different SQL versions, so I use the dummy SELECT to create an empty schema in the temp table that matches the current SQL Server definition for the INFORMATION_SCHEMA.COLUMNS view.

    I've heard of someone doing something majikal with a CTE that simply combined the views for query purposes. I'm not sure that I'd trust that... It seems sketchy to me, and the temp table solution has always worked for my needs.

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

  12. #11
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Hey Pat looks great, I was curious in the SP can you place an advanced query to join so I can pull in the additional information, such as comments for tables and columns?

    If you run my original query you can see the comments for table and columns. I really need to bring these into to define the dictionary. Thoughts? I can start up another thread if that would make things easier, however right now I was able to run your procedure and then join onto the temp table pulling in the database name. Not optimized but it works.

    Or maybe someway to encapsale the whole thing into one query or a SQL Job on SSMS.


    Code:
    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
    
    
    
    SELECT DISTINCT 
    #myColumns.Table_Catalog 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
    ,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 
    					    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')
    ORDER BY Database_Name, type_desc, TableName, syscolumns.colid
    Last edited by VLOOKUP; 04-21-15 at 13:11.

Posting Permissions

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