December 7, 2016 at 12:00 am
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
December 7, 2016 at 2:38 am
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],
December 7, 2016 at 6:53 am
Wow, what a great article. This is going to take me a while to really go over in depth.
December 7, 2016 at 7:28 am
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
December 7, 2016 at 8:03 am
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
December 7, 2016 at 10:34 am
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
December 7, 2016 at 10:42 am
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:
http://www.sqlservercentral.com/Forums/Topic1838731-433-1.aspx
On another topic:
How do you feel about this link?:
https://sqlserver.miraheze.org/wiki/RedGate%27s_Free_eBooks
412-977-3526 call/text
December 7, 2016 at 11:36 am
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
December 7, 2016 at 12:58 pm
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
October 8, 2020 at 7:09 pm
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;
January 13, 2021 at 3:31 pm
This sure looked like a great list of potential SQL Smells could be derived from this list.
412-977-3526 call/text
January 13, 2021 at 4:30 pm
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.
January 13, 2021 at 4:42 pm
Robert,
I'm not sure that I understand what you wrote. What list?
Phil
Best wishes,
Phil Factor
January 13, 2021 at 4:45 pm
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.
Phil
Best wishes,
Phil Factor
January 13, 2021 at 8:27 pm
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