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


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)
        QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME( AS [TableName],
        SUM(sdmvPTNS.row_count) AS [RowCount],
        'SELECT TOP (1) * FROM ' + QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME( +
         ' ORDER BY (1) DESC;' AS SQLDemo
        sys.objects AS sOBJ
        INNER JOIN sys.dm_db_partition_stats AS sdmvPTNS
              ON sOBJ.object_id = sdmvPTNS.object_id
        sOBJ.type = 'U'
        AND sOBJ.is_ms_shipped = 0x0
        AND sdmvPTNS.index_id < 2

The result looks similar as here.


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.


Tables and column types

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

-- Get all tables and columns in a database
SELECT AS Table_Name , AS Column_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';

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


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.