Identify Tables With Dropped Columns (2024-06-07)

SQLServerCentral Newsletter for June 7, 2024 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Identify Tables With Dropped Columns]( - [Snake draft sorting in SQL Server, part 3]( - [From the SQL Server Central Blogs - T-SQL Tuesday #174 – My Favorite Interview Question]( - [From the SQL Server Central Blogs - T-SQL Tuesday #174: Job Interview Questions]( - [The Phoenix Project]( Question of the Day - [Multi-Database Marked Transactions]( The Voice of the DBA  Poor Database Design Realities One of the interesting things that I see at Redgate Software is how idealistic our developers and engineers can be. They often build our database DevOps products with the idea that customers will use well-designed databases. The systems will have primary keys, foreign keys, defaults, constraints, indexes, and more. Developers will use coding standards, and naming conventions, and will understand what data is stored in tables. Not in every case, but often. After all, that's how we build software, at Redgate as teams, sharing information, publishing documentation for others, and following best practices. It's cute and endearing, and unfortunately, not often true. In most cases, I find databases built by developers, accidental DBAs, or even experienced DBAs to be full of inconsistencies, lacking constraints and keys, and even duplicating some indexes and forgetting others. I often joke during one of my presentations that the main thing people should learn is to add primary keys to their tables. However, I'm not really joking. During a recent design session on our masking technologies, there was a discussion on masking data in tables without PKs, which is a challenge. We're working on it, and also on being able to mask PKs themselves, as some people use the PII data as a PK. This could be a tax ID of some sort, but could also be an email address. When one of our account executives (Rob Boswell) heard that we were enhancing our capabilities with regards to PKs, he joked that we will soon be "primary key agnostic." It was a great line, and in one sense it's true. In another, it's sad that we need to design tooling around such poor practices. The reality of the world is there is a lot of bad design, bad architecture, and bad code out there. I applaud those who work to improve things in their environment, am saddened by those who don't (either improve code or their skills), and frustrated by management not supporting efforts to be better. At the very least they should support efforts to teach your staff to code things right the first time, which helps improve future code. The next best thing is to refactor and improve older code, which can help you spend less in the cloud, or run longer with the resources you have on-premises. The reality is the reality we are in, but that doesn't always need to be our future reality. We can change the future, each of us, by learning to write better code and improve how we approach our work tomorrow. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Identify Tables With Dropped Columns]( Cláudio Silva from SQLServerCentral How easily can we find tables with dropped columns that need cleanup? [External Article]( [Snake draft sorting in SQL Server, part 3]( Additional Articles from SimpleTalk In part 2 of this series, I showed an example implementation of distributing a long-running workload in parallel, in order to finish faster. In reality, though, this involves more than just restoring databases. And I have significant skew to deal with: one database that is many times larger than all the rest and has a higher growth rate. [Blog Post]( From the SQL Server Central Blogs - [T-SQL Tuesday #174 – My Favorite Interview Question]( Steve Jones - SSC Editor from The Voice of the DBA This month is a great topic to me. I think growing and improving your career is a skill that most of us could improve, especially in our younger years.... [Blog Post]( From the SQL Server Central Blogs - [T-SQL Tuesday #174: Job Interview Questions]( K. Brian Kelley from Databases – Infrastructure – Security Once upon a time, I used to think of difficult technical questions to ask candidates for a DBA position. However, over time I came to understand that in most... [The Phoenix Project]( [The Phoenix Project]( Additional Articles from SQLServerCentral In this newly updated and expanded edition of the bestselling The Phoenix Project, co-author Gene Kim includes a new afterword and a deeper delve into the Three Ways as described in The DevOps Handbook.   Question of the Day Today's question (by Steve Jones - SSC Editor):  Multi-Database Marked Transactions I run a marked transaction across multiple databases with this code: BEGIN TRAN onemorenewdbTran WITH MARK 'mark from 3 dbs' USE sandbox2 INSERT dbo.AddressTable (AddressID, AddressValue, AddressPostal) VALUES (12, '123 three St', '4444') GO USE sandbox3 INSERT dbo.Logger (logdate, logmsg) VALUES (GETDATE(), 'tran message') GO INSERT sandbox4.dbo.logger (uid) VALUES (700) COMMIT TRAN onemorenewdbTran GO How many marks are inserted into msdb.dbo.logmarkhistory? Think you know the answer? [Click here]( and find out if you are right.    Yesterday's Question of the Day (by Steve Jones - SSC Editor) Logon Trigger Messages I created this trigger in SQL Server 2022: CREATE TRIGGER checksteve ON ALL SERVER FOR LOGON AS BEGIN IF ORIGINAL_LOGIN()= N'ARISTOTLE\Steve' PRINT 'Steve logged in' END; GO Where can I view "Steve logged in"? Answer: This message is diverted to the SQL Server error log Explanation: These messages, along with error messages, are diverted to the SQL Server error log. Ref: Logon Triggers - [ [Discuss this question and answer on the forums](    Database Pros Who Need Your Help Here's a few of the new posts today on the forums. To see more, [visit the forums](. --------------------------------------------------------------- SQL Server 2017 - Administration [DB stuck in restoring state]( - A developer tried restoring a test DB 3 times and each time it was getting stuck at restoring. And when I tried restoring the DB "Restore database DBName from disk '' with recovery I ran into the same issue and I saw DB, stuck in restoring state. Sys_dm_requests doesn't show any records because I could […] [The backup to one location fails but is successful on another]( - Dear Friends, Would be glad if any of you can help in a backup related issue I am stuck in. I am trying to take a database backup to two network locations , and mapped drives. The difference between the two targets is the domain. The source SSMS session from \\a.b.c.d\backup to target \\a.b.c.e\\backup works […] [How to set execution timeout in SSMS for SQL Server]( - I tried to set the execution timeout in SSMS using multiple methods. Initially, I tried changing the query execution time through the tools option, as described on the website Subsequently, I tried a second method from the link. In the tools design settings, we can configure values for the execution timeout in seconds. […] SQL Server 2012 - T-SQL [Adapt the query to 2012]( - Hello I have a query that works as a loop but it is slow. How can I do this with a standard query? -- LINES tablosunu oluşturma IF EXISTS (SELECT 1 FROM SYS.tables WHERE name = 'LINES') BEGIN DROP TABLE LINES END GO CREATE TABLE LINES ( ID INT IDENTITY(1,1), CODE NVARCHAR(100), DATE_ DATETIME, TIP […] SQL Server 2019 - Administration [sysmail_event_log last_mod_user has a disabled login]( - Hello experts, We routinely disable sa after a SQL install. However, while troubleshooting a database mail issue (test mail not received), I saw that the sysmail_event_log entries have last_mod_user of sa for some rows. How can that be if the login is disabled? How can I change it so that sa is not used here? […] [LinkedServer error]( - I getting the bellow error when runing query on linkedserver from another server after adding linkedserver. OLE DB provider "MSOLEDBSQL" for linked server "xxx.xxx.x.xxx" returned message "Protocol error in TDS stream". OLE DB provider "MSOLEDBSQL" for linked server "xxx.xxx.x.xxx" returned message "Communication link failure". Msg -1, Level 16, State 1, Line 11 Session Provider: Connection […] [SQLCMD and the permissions needed]( - Hey all, Hopefully this is a simple question. I am trying to figure out what permissions are needed for an account to use SQLCMD. I understand sysadmin allows use of it, but we are trying to not grant this non-user account sysadmin. I have been googling and either I'm not using the right keywords or […] SQL Server 2019 - Development [Filtered index prerequisites to be used]( - Hello, After few videos and showing how filtered indexes works, what are the prerequisites to be used and so on still I am not able to make my query using it. Now, I have table CDPOS which is over 100 milion rows. There is a statement which will run automatically, we are still in deciding […] [Monitoring if something is deleted in DB]( - Hello, I have long-term task, which came one year ago, and just now I have a little free time to start working on it. I will explain a little bit of the task, and then what I have done so far. Some time ago, even before I worked here, colleagues of mine using the web-application […] Powershell [Invoke-SQLCMD]( - If my server has dashes seems like that is throwing an error -serverinstance at-ts-cblsql  is server name Anyway to get around that? THanks Continuous Integration, Deployment, and Delivery [Red Gate SQL Change Automation]( - Hi, What's happened to this product has it now become Flyway? [Red Gate SQL Change Automation]( - Hi, What's happened to this product? has this now become Flyway?  Suggestions [How to change password]( - How can I change login password of this website? I don't see option under my profile, and google give no result. Is it even possible to change password? SQL Server 2022 - Development [singleton is interferring with getting multiple nodes in xml]( - Hi we have an xml doc shaped essentially as shown in image 2 below.  Unfortunately we are so used to using the singleton structure to avoid problems that we dont know how to get multiple nodes like those inside featuresandoptions . At the moment we are only getting one occurrence of each for each line […] [Calculating moving moving averages for different ranges in one query]( - I have this query, which is gives result I want (may be not, i have not checked extensively yet), i feel like it could be written in more effective way. I have to calculate moving average, for 7 , 90, 365 days, and for 3 and 12 month, for every one of them I have […]   [RSS Feed]( This email has been sent to {EMAIL}. To be removed from this list, please click [here](. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com. ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -