T-SQL Scripts to generate the HTML-formatted database storage report (2023-06-12)

SQLServerCentral Newsletter for June 12, 2023 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [T-SQL Scripts to generate the HTML-formatted database storage report]( - [Flyway for SQL Server Data Tools Users]( - [Multiple Relationships on the same Table in a Power BI Data Model]( - [From the SQL Server Central Blogs - Build announcement: Microsoft Fabric]( - [From the SQL Server Central Blogs - SQL Server Backup Encryption and Compression]( - [Practical Database Auditing for Microsoft SQL Server and Azure SQL: Troubleshooting, Regulatory Compliance, and Governance]( Question of the Day - [Getting the Winners]( The Voice of the DBA  Using the Lightweight Tools Today's editorial was originally published on Dec 19, 2019. It is being re-published as Steve is out of town. I'm torn on [Azure Data Studio]( (ADS). On one hand, I love the quick start and lightweight nature of the tool. On the other hand, the limited GUI and unfamiliar way of working with a database platform like SQL Server bother me a bit. Lacking some of the thick client features has me mostly using it for small things, like PostgreSQL access or notebooks. Even then, I struggle to leave [SSMS](. Maybe I've just had too many years of SSMS (and Enterprise Manager before it), to easily change. While the ADS/SSMS relationship seems to lean towards the latter, that I just have too much history with SSMS to change. I keep hearing the [VS Code]( is way more popular and useful than [Visual Studio]( from many people. I'm not sure that this means most people that use Visual Studio have abandoned it for VS Code, but certainly VS Code usage has grown quite a bit. Even [Facebook has made it the default development environment](. I'd have thought this wasn't as popular in non-Microsoft stack places, but VS Code appears to compete well with Sublime Text, Atom, Notepad++, Vim, and others. This week, I wonder if many of you feel that these new lightweight editors are better than the heavyweight thick clients. Do you prefer ADS or SSMS? VS Code or VS? Are there any limitations or features that might sway or change your mind? I certainly like VS Code, and have even moved PoSh work there instead of the ISE, one place where lightweight tooling wins for me. I work for Redgate, and we are doing some work to build tools for ADS. There is a market there, but it seems very small for now. Perhaps that is changing, but I'm not sure that usage will dramatically grow in the next year. If you feel differently, or wish things would change, let me know. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [T-SQL Scripts to generate the HTML-formatted database storage report]( Nisarg Upadhyay from SQLServerCentral This article will show how to generate an HTML formatted report of your disk space. [External Article]( [Flyway for SQL Server Data Tools Users]( Additional Articles from Redgate If you are using SSDT for authoring, building, debugging, and publishing a database project, how do you change to, or preferably migrate towards, a Flyway-based database development? Flyway doesn't need to replace any code part of SSDT, but if allowed to manage every release candidate, it does allow for much cleaner branching, merging, and deployments. [External Article]( [Multiple Relationships on the same Table in a Power BI Data Model]( Additional Articles from MSSQLTips.com This article will cover how to create multiple relationships between the same tables in Power BI to build the data model you need. [Blog Post]( From the SQL Server Central Blogs - [Build announcement: Microsoft Fabric]( James Serra from James Serra's Blog The HUGE announcement at Microsoft Build yesterday was Microsoft Fabric (see Introducing Microsoft Fabric: Data analytics for the era of AI), now available in public preview. I have been... [Blog Post]( From the SQL Server Central Blogs - [SQL Server Backup Encryption and Compression]( Matthew McGiffen from Matthew McGiffen DBA In SQL Server you can also compress your encrypted backups. Unlike TDE this has been possible with Backup Encryption since the feature was first made available, and there have... [Practical Database Auditing for Microsoft SQL Server and Azure SQL]( [Practical Database Auditing for Microsoft SQL Server and Azure SQL: Troubleshooting, Regulatory Compliance, and Governance]( Site Owners from SQLServerCentral Know how to track changes and key events in your SQL Server databases in support of application troubleshooting, regulatory compliance, and governance. This book shows how to use key features in SQL Server ,such as SQL Server Audit and Extended Events, to track schema changes, permission changes, and changes to your data. You’ll even learn how to track queries run against specific tables in a database. ss   Question of the Day Today's question (by Steve Jones - SSC Editor):  Getting the Winners I get a JSON string that looks like this: DECLARE @JSON NVARCHAR(MAX) = N'{ "WinnerIDs": [11,14,15,16,45], "Race": "Furry Scurry", "RaceStartDate": "2022-05-20T08:00:00", "RaceEndDate": "2022-05-20T10:00:00", "Entries": 100 }'; I want to parse out the values of the winners to show each on a separate line of the result set. I try a couple of things. First, this: SELECT oj.Race, oj.WinnerIDs FROM OPENJSON (@JSON) WITH (Race NVARCHAR(100) '$.Race' , WinnerIDs NVARCHAR(MAX) '$.WinnerIDs' AS JSON) oj Second, this: SELECT oj.Race, oj2.WinnerIDs FROM OPENJSON (@JSON) WITH (Race NVARCHAR(100) '$.Race' , WinnerIDs NVARCHAR(MAX) '$.WinnerIDs' AS JSON) oj INNER JOIN OPENJSON (@JSON) WITH (Race NVARCHAR(100) '$.Race' , WinnerIDs NVARCHAR(MAX) '$.WinnerIDs' AS JSON) oj2 ON oj.WinnerIDs = oj2.WinnerIDs Third this: SELECT oj.Race, oj.[value] AS WinnerID FROM OPENJSON (JSON_QUERY(@JSON, '$.WinnerIDs')) AS oj Fourth and lastly, this: SELECT oj.Race, oj2.[value] AS WinnerIDs FROM OPENJSON (@JSON) WITH (Race NVARCHAR(100) '$.Race' , WinnerIDs NVARCHAR(MAX) '$.WinnerIDs' AS JSON) oj CROSS APPLY OPENJSON(oj.WinnerIDs) AS oj2 Which of these returns these results? Race WinnerIDs Furry Scurry 11 Furry Scurry 14 Furry Scurry 15 Furry Scurry 16 Furry Scurry 45 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) Secure Enclave Attestation What does secure enclave attestation do in Always Encrypted? Answer: Allows the client to establish trust with the secure enclave Explanation: From Docs: Enclave attestation allows a client application to establish trust with the secure enclave for the database, the application is connected to, before the app uses the enclave for processing sensitive data. Ref: Secure enclave attestation - [ [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 [Vulnerability Assessment removed?]( - In Brent Ozar's list of links today, I learned that Microsoft has removed the Vulnerability Assessment tool from SSMS as of v19.1. I recently started using it in my SQL Server environment and found it a great tool -- I was able to demonstrate to management why some of their security shortcuts were a bad […] SQL Server 2017 - Development [Need help with a Query logic]( - I have a table which tracks of activity when users from one specific app login and logout. But I have seen some cases where let's say a user logins at 11:40 on March 24th and then logins again at 11:55 on the same day without logging out. The application doesn't allow that but I am […] SQL Server 2016 - Administration [Database copy issue through - COPY DB WIZARD]( - I have a production server with SQL server 2016 version (13.0.5026.0) and a database with 25 GB of Data and 525 tables in it. Now, I want to transfer this production DB to my development environment with SQL server 2016 version (13.0.5026.0). I am using DB copy wizard for DB copy to the new instance […] SQL Server 2016 - Development and T-SQL [Help with a SQL Unpivot]( - I am trying to use unpivot to extract some data, however, the results are getting messed up on at least one record. By this I mean it is out of order which it should be CLNUM: MODEL: CLSTATUS: CLSTATDATE: CLSTOP: Here is the query any help or advice would be appreciated. WITH ClientsWithOpenMattersCTE (ClientNo, HowMany) […] [SQL help with partition]( - With the following data, I need to create a column that will display a counter for each VID, Acc where PrgFld is not 'XXX' I am trying to do this with partition, but it's increasing the counter when PrgFld is XXX. I would prefer to not increase the counter or show 0 for XXX. Is […] [NOT LIKE Alternatives in WHERE clause]( - Hello, I need help to improve an existing (hence I cannot change table's structure nor the content's logic) WHERE clause, please. The table consists of returned products with their respective reason(s). In case there is more than one reason, they are concatenated. The request as shown in the mock-up below, has to retrieve a list […] Administration - SQL Server 2014 [Database backups going to device]( - Hi Friends, How can I know the exact device where my backups are going on? I only see {67BAB2D0-A1DC-44F8-BF73-EBAFD5AE3220}16 as one device. However, I do not find out where are my backup files located. Some databases are going to a particular drive, but others are using that expression, Thank you, Best Regards, SQL Server 2019 - Administration [Upgrading from 2016 to 2019 with Encryption]( - Hi, I have a few AWS EC2 instances currently running SQL Server 2016. I'd now like to upgrade these to SQL Server 2019 but the problem is that some of the data has been encrypted at the column level. I understand that, after SQL 2016, the encryption algorithm changed from SHA1 to SHA2. Would this […] SQL Server 2019 - Development [how to de escalte a isolation level, and are there any implications.]( - hi, I have one nested transaction and want to deescalate to outer transaction's isolation/ or lower isolation. is it the right way? SET TRANSACTION ISOLATION LEVEL read committed; BEGIN TRY BEGIN TRAN DBCC USEROPTIONS --want to do some job in read commited SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; BEGIN TRY BEGIN TRAN DBCC USEROPTIONS --want to […] [Advanced SQL query help]( - Hi, I am looking for some help with two queries using the following table/data. It would be great to see the most efficient way to achieve these. Queries 1: Show all homes with rent above the average rent value for their Company 2: Show the number of homes registered 0-3 months, 4-6 months, 6-12 months, […] [MSSQL Service Broker DSQL operation on another database causing Broker failure]( - I have established a Service Broker configuration based on Eitan Blumin's excellent example. Advanced Service Broker Sample: Asynchronous Triggers I have crafted my own Stored Procedure. I can get the example to work when I'm performing activities within the same database. If I try to operate outside the current DB I get errors. I'm new […] [Extracting multiple fields from one column]( -   Hello, I have a situation. I am trying to show each distinct event category, event action and event label from 'Hits' column and show the number of times each event occurred and the month that the event occurred– for labels containing “GNAV”. I am not sure why but something is wrong. Do you have […] Analysis Services [How can I show the last opening period for each project in my project dimension]( - I have two attributes hierarchies in my dimension [Dim Project]: Project Code Opening date Project A with 3 opening date : 2023-01-01 , 2023-01-02 and 2023-01-03 Project B with 2 opening date : 2023-01-02 , 2023-01-04 MDX query : Select [Dim Project].[Project Code] .[Project Code].Allmembers * [Dim Project].[Opening date].[Opening date].Allmembers on 0 [Measures].[Measure1] on 1 […] SQL Server 2022 - Administration [Allow only encrypted connections.]( - Hi In SQL22, Is there a way to allow only / force encrypted connections? I have 'force encryption' on and have cert installed:  But when I connect via SSMS I can unselect ' Encrypt Connection' and it connects. My understanding is that means that it is allowing non-encrypted connections: How do I make my […] SQL Server 2022 - Development [SQL Query]( - Hi, i have a sample table: table ID  flag  name 1    0   test1 1    0   test2 1    1   test3 2    0   test4 2    0   test5 3    1   test6 4  […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -