blog.atwork.at

news and know-how about microsoft, technology, cloud and more.

Get data from SQL Server for GDPR and more

Getting ready for GDPR? Well, often data is stored in relational databases as in SQL Server - in the cloud or on premises. If you need to report what relevant data is stored in such a SQL database, the following scripts can help.

Often, a data processing document is needed between the data controller (often the customer) and the data processor (the IT department or a vendor). So, how to get data for a review? Here we go with a little bit of TSQL and SQL Server Management Studio (SSMS) or SQL Operations Studio. Connect to the corresponding database and try the following commands.

Database size

To get an overview of size of a database, use:

SELECT (SUM(reserved_page_count) * 8192) / 1024 / 1024 / 1024.0 AS DbSizeInGB
FROM sys.dm_db_partition_stats

image

Records in tables (and demo data)

To get an overview of the number of records stored in all tables of a database, use:

-- Get the number of records of all tables with sys.dm_db_partition_stats Dynamic Management View (DMV)
-- https://www.mssqltips.com/sqlservertip/2537/sql-server-row-count-for-all-tables-in-a-database/
SELECT
        QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName],
        SUM(sdmvPTNS.row_count) AS [RowCount],
        'SELECT TOP (1) * FROM ' + QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) +
         ' ORDER BY (1) DESC;' AS SQLDemo
FROM
        sys.objects AS sOBJ
        INNER JOIN sys.dm_db_partition_stats AS sdmvPTNS
              ON sOBJ.object_id = sdmvPTNS.object_id
WHERE
        sOBJ.type = 'U'
        AND sOBJ.is_ms_shipped = 0x0
        AND sdmvPTNS.index_id < 2
GROUP BY
        sOBJ.schema_id, sOBJ.name
ORDER BY [RowCount] DESC;
GO

The result looks similar as here.

image

For convenience, I added a SQLDemo column to quickly get some sample data from each table if needed. So, if you need examples of the data, execute the generated SQL statements as in this sample here.

image

Tables and column types

To get all tables and their columns in a list, use:

-- Get all tables and columns in a database
SELECT T.name AS Table_Name ,
        C.name AS Column_Name ,
        P.name AS Data_Type ,
        P.max_length AS Size ,
        CAST(P.precision AS VARCHAR) + '/' + CAST(P.scale AS VARCHAR) AS Precision_Scale
FROM   sys.objects AS T
        JOIN sys.columns AS C ON T.object_id = C.object_id
        JOIN sys.types AS P ON C.system_type_id = P.system_type_id
WHERE  T.type_desc = 'USER_TABLE';
GO

The result looks similar as here: All columns per table.

image

From here, you can continue to work with that data, e.g. in Excel or or other tools.

See more about SQL Server at the resources here:

Hope this helps for your documentation as a data processor.

Loading