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

    Answered: System Queries version 3

    Pat good ole buddy!

    The system query that generates queries for all the databases, do you think we could set that into SQL server agent to update/insert into one table and or seperate tables? If so I would build a data dictionary database and keep the seperate tables in there.

    Do you have an approach on this? I'm pretty excited to see the progress, you are talented man!

  2. Best Answer
    Posted by Pat Phelan

    "After you plug in a few database and table names, this ought to work:
    Code:
    --  ptp  20150624  Build a database dictionary snapshot
    
    --  Build/truncate the table
    
    USE _Only_the_Database_Name_for_your_Data_Dictionary_goes_here_
    GO
    
    IF Object_Id('_Only_the_Data_Dictionary_table_name_goes_here_', 'U') IS NOT NULL
       TRUNCATE TABLE _Your_Data_Dictionary_table_name_goes_here_
    ELSE
    SELECT
       Cast('?' AS sysname) 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
       INTO _Your_Data_Dictionary_table_name_goes_here_
       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  0 = 1
    
    --  Populate the table
    
    INSERT INTO _Your_Data_Dictionary_table_name_goes_here_
    EXECUTE sp_msforeachdb '
    SELECT
       Cast(''?'' AS sysname) 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
    '
    
    SELECT * FROM _Your_Data_Dictionary_table_name_goes_here_
    -PatP"


  3. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Oh dear, now you've really opened a can of worms! The answer is the same as before, "it depends".

    Let's start with the questions:
    1. Have you checked out SQL-Spec? It seems like a better answer for this.
    2. Do you want to rebuild every run, or maintain a long term reference showing changes over time?
    3. One table per SQL Instance (with database as a column) is best. Is that Ok?
    4. Table means SQL must be available to use this information, no portability, no DR. That's distinctly sub-optimal
    5. Anyone who can access the table can see everything... Is that Ok?


    -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
    1. Noted
    2. Rebuild every run
    3. Yes that is fine
    4. Ok
    5. Yes this is fine.

  5. #4
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Pat it just hit me when I was creating a job for the last query.

    I could stick with just the individual database query that pulls everything through (description etc).

    I would create a schema for the data dictionary or somehow organize it and create a job with multiple steps. I could put the query in each step and change the name of the into statement to Database1_Data_Dictionary, etc....... while adjusting the drop down on which database I want that step to run under. This would be ~9 steps for 9 databases and I could create a drop script. Drop Table IF Exists table1; etc......

    Thoughts on this approach?

  6. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    After you plug in a few database and table names, this ought to work:
    Code:
    --  ptp  20150624  Build a database dictionary snapshot
    
    --  Build/truncate the table
    
    USE _Only_the_Database_Name_for_your_Data_Dictionary_goes_here_
    GO
    
    IF Object_Id('_Only_the_Data_Dictionary_table_name_goes_here_', 'U') IS NOT NULL
       TRUNCATE TABLE _Your_Data_Dictionary_table_name_goes_here_
    ELSE
    SELECT
       Cast('?' AS sysname) 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
       INTO _Your_Data_Dictionary_table_name_goes_here_
       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  0 = 1
    
    --  Populate the table
    
    INSERT INTO _Your_Data_Dictionary_table_name_goes_here_
    EXECUTE sp_msforeachdb '
    SELECT
       Cast(''?'' AS sysname) 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
    '
    
    SELECT * FROM _Your_Data_Dictionary_table_name_goes_here_
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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