Exploring Facts About SQL Server Tables: Stairway to Exploring Database Metadata Level 5

  • Comments posted to this topic are about the item Exploring Facts About SQL Server Tables: Stairway to Exploring Database Metadata Level 5

    Best wishes,
    Phil Factor

  • just for convenience,

    coalesce(object_schema_name(Referencing_ID)+'.','')+ --likely schema name

    object_name(Referencing_ID)+ --definite entity name

    coalesce('.'+col_name(referencing_ID,referencing_minor_id),'') AS [referencing],

    may be written as

    concat(object_schema_name(Referencing_ID)+'.', --likely schema name

    object_name(Referencing_ID), --definite entity name

    '.'+col_name(referencing_ID,referencing_minor_id) ) AS [referencing],

  • Wow, what a great article. This is going to take me a while to really go over in depth.

  • Thanks. @IwasBornready

    This series has been received quite quietly so far, so it is nice to know that someone appreciates them!

    Best wishes,
    Phil Factor

  • Which of the scripts do you think I need permission to reproduce on my company wiki?

    Do I have your permission? Was this written in the course of you employment or as an independent contributor?

    412-977-3526 call/text

  • @robert,

    This is fine on a company Wiki that is accessible only to your company, with an attribution to the source. Redgate, who own this site, are only concerned with protecting authors' intellectual property on this site against any unauthorised publishing: 'Publishing' means making the authors' property publicly available in a website, book, presentation or whatever without consent of the author. A private group such as your company is fine as long as you mention where it came from. I need the clicks!

    SSC have permission to be sole publishers of this article, which remains my Intellectual property. This arrangement allows SSC to prevent rogue sites from copying our content.

    A lot of people collect scripts from SSC to use at work, and make available to others within the workplace. That's what SSC is for.

    I hope that answers the question!

    Best wishes,
    Phil Factor

  • Unfortunately the policy requires that I ask permission for each copyrighted script or article that I want to copy.

    I'd be grateful if you took a look at my forum item about the terms of use:


    On another topic:

    How do you feel about this link?:


    412-977-3526 call/text

  • @robert

    I expect that RedGate would be happy about the link because they are linking to the Redgate site. Where might be the problem?

    Re: copyright: I'm no lawyer, but I reckon that the SSC site is fine about you using SSC content for private use, including the workplace. I would positively like you to use it. After all, code, and sharing code, is what the site is about. In some cases, there is only one correct way of coding a particular routine, and it is almost impossible to determine who thought of the idea of doing things a certain way. Anyway, I suspect that your company would be relaxed if you only have to ask each author once. You are welcome to use any of my code for private or workplace use. I guess all authors would be the same but I can't speak for them. The problem with any site like this is that it relies on loads of contributions and so the publishers, Redgate, own publication rights, but not the IP. How do you suggest it should work? I know that Redgate only take ownership of publication rights so they can act on the authors behalf to prevent plagiarism. Surely, they can't give anyone the blanket rights to copy content that doesn't belong to them? Nobody is concerned with everyday use of code, but would worry about re-publishing or plagiarism.

    Best wishes,
    Phil Factor

  • My main hope is that people actively address the copyright of their scripts separately from their articles.

    Give them a list of options when they publish a script:

    1) Not under copyright

    2) creative commons and its variants

    3) copyrighted

    It would be helpful if a license line for the content read something like this:

    Material licensed from copyright holder: X

    412-977-3526 call/text

  • Thought your script on clustered GUIDs would want to ignore system objects - it dropped my positives from 10 to 1.

    SELECT OBJECT_SCHEMA_NAME( IC.Object_ID ) + '.' + OBJECT_NAME( IC.Object_ID ) AS TheTable,
    COL_NAME( IC.Object_Id, IC.Column_Id ) + ' is a GUID in a clustered index' AS Smell /* GUID in a clusterd IX */
    FROM Sys.Index_Columns AS Ic
    INNER JOIN sys.all_objects AS AO ON IC.object_ID = AO.Object_ID
    INNER JOIN sys.columns AS SC ON IC.object_ID = SC.object_ID AND IC.column_ID = SC.column_ID
    INNER JOIN sys.types AS ST ON SC.system_type_id = ST.system_type_id
    INNER JOIN sys.indexes AS SI ON IC.object_ID = SI.object_ID AND IC.index_ID = SI.index_ID
    WHERE ST.name = 'uniqueidentifier'
    AND SI.type_desc = 'CLUSTERED'
    AND OBJECTPROPERTY( IC.OBJECT_ID, 'IsSystemTable') = 0 AND AO.Is_ms_shipped = 0;

    • This reply was modified 3 years, 6 months ago by  SQLOzzie.
    • This reply was modified 2 years, 1 month ago by  SQLOzzie.
  • This sure looked like a great list of potential SQL Smells could be derived from this list.

    412-977-3526 call/text

  • Thanks for this series. I have most of this logic and more in views which I use interactively as well as from a python script to generate a spreadsheet of metadata with multiple sheets containing different sets of information formatted as tables to make it easier to filter for searches. I normally run it for a schema and all tables or all schemas and tables. Have you thought about possibly packaging these up in a similar manner? If so, I would love to help out. I can't post mine as they are copyrighted but I can definitely help package up yours.

  • Robert,

    I'm not sure that I understand what you wrote.  What list?


    Best wishes,
    Phil Factor

  • @pwo311

    Thank you for your interest. Perhaps you can show an example of the sort of thing you have in mind. Since writing this article, I've done a lot more work on exploring metadata so I might have something already that would provide a basis.


    Best wishes,
    Phil Factor

  • Finding potential problems in tables

    The tables that are wide (more than 15 in this example; you can modify to taste)

    Tables that are heaps

    Tables that are undocumented using extended properties

    Tables without a Primary Key

    Tables with no indexes at all

    Tables with no candidate key (unique constraint on column(s))

    Tables with disabled Index(es)

    Tables with disabled constraint(s)

    Tables with untrusted constraint(s)

    Tables with a disabled Foreign Key(s)

    Tables with untrusted Foreign Key(s)

    Tables unrelated to any other table

    Tables with unintelligible column names

    Tables with a foreign key that has no index

    Tables with a GUID in a clustered Index

    Tables with non-compliant column names

    Tables with a trigger that hasn’t got NOCOUNT ON

    Tables that are not referenced by any procedure, view or function

    Tables with a disabled trigger

    Tables that can't be indexed

    Table has unique constraint that is NULLable

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply