Category: SQL

To get an overview of total disk space allocated per table in MB, use this SQL script:

WITH TableSizes AS
(
    SELECT 
        sch.name AS SchemaName,
        t.name AS TableName,
        SUM(p.rows) AS RowCounts,
        SUM(a.total_pages) * 8 AS TotalSpaceKB, 
        SUM(a.used_pages) * 8 AS UsedSpaceKB,
        (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
    FROM 
        sys.tables t
    INNER JOIN 
        sys.indexes i ON t.object_id = i.object_id
    INNER JOIN 
        sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
    INNER JOIN 
        sys.allocation_units a ON p.partition_id = a.container_id
    INNER JOIN 
        sys.schemas sch ON t.schema_id = sch.schema_id
    WHERE 
        t.type = 'U' -- Only include user tables
    GROUP BY 
        sch.name, t.name
)
SELECT 
    SchemaName,
    TableName,
    RowCounts,
    --TotalSpaceKB,
    --UsedSpaceKB,
    --UnusedSpaceKB,
    CAST(TotalSpaceKB / 1024.0 AS DECIMAL(10, 0)) AS TotalSpaceMB
    --CAST(UsedSpaceKB / 1024.0 AS DECIMAL(10, 2)) AS UsedSpaceMB,
    --CAST(UnusedSpaceKB / 1024.0 AS DECIMAL(10, 2)) AS UnusedSpaceMB
FROM 
    TableSizes
ORDER BY 
    TotalSpaceKB DESC;

You can decide how SQL-Developer display date and timestamp columns.

1. Go to the “Tools” menu and open “Preferences…”
2. In the tree on the left open the “Database” branch and select “NLS”
3. Now change the entries “Date Format”, “Timestamp Format” and “Timestamp TZ Format” as you wish!

ISO formated output with date and time

• Date Format: YYYY-MM-DD HH24:MI:SS
• Timestamp Format: YYYY-MM-DD HH24:MI:SSXFF
• Timestamp TZ Format: YYYY-MM-DD HH24:MI:SSXFF TZR

Source: SQL-Developer: How to change the date format of the result grid – “Why does my date column have no time?” | Oracle Deli

Open SQL Server Management Studio and connect to the db server then follow these steps:

Source: View or Change the Default Locations for Data and Log Files | Microsoft Docs

The following SQL will get you the row count of all tables in a database:

CREATE TABLE #counts
(
    table_name varchar(255),
    row_count int
)

EXEC sp_MSForEachTable @command1='INSERT #counts (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?'
SELECT table_name, row_count FROM #counts ORDER BY table_name, row_count DESC
DROP TABLE #counts

The output will be a list of tables and their row counts.

If you just want the total row count across the whole database, appending:

SELECT SUM(row_count) AS total_row_count FROM #counts

will get you a single value for the total number of rows in the whole database.

Source: How to fetch the row count for all tables in a SQL SERVER database – Stack Overflow

…there are a few options that can help us to downgrade the database from a higher version of SQL Server to a lower version SQL Server. These options include:

• Generate Scripts wizard of SQL Server Management Studio
• SQL Server Integration Services
• Custom scripting and BCP

In this tip we will use the Generate Scripts wizard of SQL Server Management Studio.

Source: How to migrate a SQL Server database to a lower version

I needed to search in all tables for a certain GUID, I used this SP in MS SQL Server 2016: Find a GUID (or anything) in ANY table in your database! | CSharpner.com

See heading: Using SQL Server Export / Import wizard

Source: How to copy tables from one database to another in SQL Server – SQL Shack – articles about database auditing, server performance, data recovery, and more