Finding Dependencies On Tables

Kevin Feasel

2018-07-09

Naming

Lori Brown shows us a few methods for finding references to tables:

I use sys.sql_modules to check for references to strings in stored procedures. You might think that this would not be necessary since you can find that in sys.dm_sql_referencing_entities for those. However, what if you do a lot of stuff in stored procedures that uses dynamic SQL to create objects or other stored procs on the fly? sys.dm_sql_referencing_entities might miss those. For example, when I look in sys.sql_modules for all occurrences of Sales.Orders I find more.

This is necessarily an incomplete list, but at least it gets you started.

Missing @@SERVERNAME On Linux

Steve Jones fixes a naming issue on his SQL on Linux installation:

I setup a new instance of SQL Server on Linux some time ago. At the time, the Linux machine didn’t have any Samba running, and no real “name” on the network. As a result, after installing SQL Server I got a NULL when running SELECT @@SERVERNAME.

The fix is easy. It’s what you’d do if you had the wrong name.

Read on for the command, and don’t forget to restart the database engine afterward.

Naming Indexes

Monica Rathbun hits one of my hobby horses:

As you can see from above, none of the names gave a complete indication of what the index encompassed. The first one did indicate it was a Non Clustered Index so that was good, but includes the date which to me is not needed. At least I knew it was not a Clustered Index. The second index did note it is a “Covering Index” which gave some indication that many columns could be included I also know it was created with the Data Tuning Advisor due to the dta prefix. The third index was also created with dta but it was left with the default dta naming convention. Like the first one I know the date it was created but instead of the word Cover2, I know there are 16 key columns are noted by the “K#” and regular numbers tell me those are included columns. However, I still have no idea exactly what these numbers denote without looking deeper into the index. The last index is closer to what I like however the name only tells me one column name when in fact it encompasses five key columns and two included columns.

I absolutely love seeing lots and lots of “_dta_” indexes; it’s a sign that I have a long day ahead of me.

Altering Procedures And Object Definitions

Kevin Feasel

2018-01-11

Naming

Solomon Rutzky shows what metadata doesn’t get updated when you call sp_rename on a T-SQL procedure, function, view, or trigger:

This behavior is noted in the Microsoft documentation for sp_rename:

Renaming a stored procedure, function, view, or trigger will not change the name of the corresponding object name in the definition column of the sys.sql_modules catalog view. Therefore, we recommend that sp_rename not be used to rename these object types. Instead, drop and re-create the object with its new name.

Ok, so we have all been warned, at least when it comes to using sp_rename. But that is not the end of the story. There is, indeed, another way to change the object such that the definition does not reflect its current state. And that other way has to do with something missing from the examples shown thus far, something that wouldn’t be missing had I been following best-practices.

Click through to see what else doesn’t get updated.

Generating Fake Company Names

Kevin Feasel

2017-09-19

Naming

Daniel Hutmacher has a great way of generating fake company names:

This query is actually a lot simpler than it first appears. Here’s how it breaks down:

  • Pick 100 words at random (table “a”)

  • For each word in “a”, if possible, pick a single random word (“b”) that doesn’t start or end with the same three letters as the “a” word.

  • For each word in “b”, if possible, pick a single random word (“c”) that doesn’t start or end with the same three letters as the “a” nor the “b” word.

  • The UPPER(), LEFT() and SUBSTRING() stuff is just to turn the names into title case.

  • As before, the ORDER BY NEWID() randomizes the order in which the TOP (1) row is returned.

My favorite name when running this was Disaster Votes, followed closely by Fail Users Vendor and Terminated Enterprise.  Apparently my SQL Server instance has a very negative impression of my made up companies’ leadership skills.

Names Matter

Kevin Feasel

2017-09-15

Naming

Chris Webb is concerned about the under-use of the name “Power Query”:

In more recent times I’ve written posts with unwieldy names like “Introduction to Insert Topic Name Here in Power Query/Power BI/Excel 2016 Get & Transform” and in the future I suppose this will have to grow to “Introduction to Insert Topic Name Here in Power Query/Power BI/Excel 2016 Get & Transform/Analysis Services Data Loading/Common Data Service”. Tagging and categorising blog posts can help here, I know, but it’s the title of a blog post that’s the main determining factor as to whether it gets read or not when someone is looking at a list of search results. It’s getting ridiculous, but how else can I ensure that someone searching for the solution to a data loading problem in Excel 2016 Get & Transform will find a post I’ve written that contains the answer but shown in Power BI?

Commenters agree that there’s a lot of ambiguity and multiple naming, and that this hurts end users who don’t necessarily know that all of these technologies are really the same Power Query engine.

Naming Graph Edges

Greg Low is trying to find a common nomenclature for edges in graphs:

Positive (Forward) Direction

I’d also like to see the tables use a forward direction naming rather than reverse (like “Written By”). So perhaps:

($from_id) the member Wrote the post ($to_id)

($from_id) who Likes who/what ($to_id)

($from_id) the reply to the main post RepliesTo the main post ($to_id)

Avoid passive voice.  That’s good advice in general.

Spell Checking With Visual Studio Code

Andy Levy shows how to create a custom dictionary for a programming language in Visual Studio Code:

But as you can see from the marketplace page there, by default this plugin doesn’t know PowerShell. In my user settings file settings.json, I added PowerShell to the cSpell.enabledLanguageIds section so it’s always recognized:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
"cSpell.enabledLanguageIds": [
        "c",
        "cpp",
        "csharp",
        "go",
        "javascript",
        "javascriptreact",
        "json",
        "latex",
        "markdown",
        "php",
        "plaintext",
        "powershell",
        "python",
        "text",
        "typescript",
        "typescriptreact",
        "yml",
        "powershell"
    ],

And with that, VSCode was giving me green squiggles under lots of words – both misspelled and not. Code Spellchecker doesn’t understand PowerShell in its default setup, it doesn’t have a dictionary for it. Just to get things started, I added a cSpell.userWords section to my settings.json and the squiggles started disappearing.

It’s an interesting post, so read the whole thing.

Picking An R Package Name

Kevin Feasel

2017-05-11

Naming, R

Marcelo Perlin has fun looking at package names in CRAN:

Looking at package names, one strategy that I commonly observe is to use small words, a verb or noun, and add the letter R to it. A good example is dplyr. Letter d stands for dataframe, ply is just a tool, and R is, well, you know. In a conventional sense, the name of this popular tool is informative and easy to remember. As always, the extremes are never good. A couple of bad examples of package naming are A3, AF, BB and so on. Googling the package name is definitely not helpful. On the other end, packagesamplesizelogisticcasecontrol provides a lot of information but it is plain unattractive!

Another strategy that I also find interesting is developers using names that, on first sight, are completely unrelated to the purpose of the package. But, there is a not so obvious link. One example is package sandwich. At first sight, I challenge anyone to figure out what it does. This is an econometric package that computes robust standard errors in a regression model. These robust estimates are also called sandwich estimators because the formula looks like a sandwich. But, you only know that if you studied a bit of econometric theory. This strategy works because it is easier to remember things that surprise us. Another great example is package janitor. I’m sure you already suspect that it has something do to with data cleaning. And you are right! The message of the name is effortless and it works! The author even got the privilege of using letter R in the name.

Marcelo uses word and character analysis to come up with his conclusions, making this a good way of seeing how to graph and slice data. h/t R-bloggers

What’s In A Name?

Kevin Feasel

2017-03-21

Naming

Kenneth Fisher explains the different parts of an object name in SQL Server:

One Part Name
vIndividualCustomer
This is just the name of the object. This is probably the most common usage and yet the only one I would recommend never using. (I’ll freely admit I’m not great at this myself btw.) When you only use a single name the schema is implied by the default schema of the user running the statement. That means that the same statement run by two different users could have two different meanings. Since most users have a default schema of dbo this would probably hit the object dbo.vIndividualCustomer. Assuming one even existed.

Click through for more.

Categories

August 2018
MTWTFSS
« Jul  
 12345
6789101112
13141516171819
20212223242526
2728293031