November 2018 – Run SQL Server BLOG

Default Null értékű oszlopok , ahol minden Null értékű:

    -- Returns a list of all columns in current database
    -- where the column's value is null for all records.
    
	declare @tempTable TABLE
    (
        TableSchema nvarchar(256),
        TableName nvarchar(256),
        ColumnName sysname,
        NotNullCnt bigint
    );
     
    declare @sql nvarchar(4000);
    declare @tableSchema nvarchar(256);
    declare @tableName nvarchar(256);
    declare @columnName sysname;
    declare @cnt bigint;
     
    declare columnCursor cursor FOR
        SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
        WHERE 
		-- TABLE_NAME ='tablename' and
		IS_NULLABLE = 'YES';
     
    open columnCursor;
     
    fetch next FROM columnCursor INTO @tableSchema, @tableName, @columnName;
     
    while @@FETCH_STATUS = 0
    begin
        -- use dynamic sql to get count of records where column is not null
        SET @sql = 'select @cnt = COUNT(*) from [' + @tableSchema + '].[' + @tableName +
            '] where [' + @columnName + '] is not null';
        print @sql; --uncomment for debugging
        exec sp_executesql @sql, N'@cnt bigint output', @cnt = @cnt output;
     
        INSERT INTO @tempTable SELECT @tableSchema, @tableName, @columnName, @cnt;
     
        fetch next FROM columnCursor INTO @tableSchema, @tableName, @columnName;
    end
     
    close columnCursor;
    deallocate columnCursor;
     
    SELECT * FROM @tempTable WHERE NotNullCnt = 0;

-- Returns a list of all columns in current database

-- where the column's value is null for all records.

declare @tempTable TABLE

(

TableSchema nvarchar(256),

TableName nvarchar(256),

ColumnName sysname,

NotNullCnt bigint

);

declare @sql nvarchar(4000);

declare @tableSchema nvarchar(256);

declare @tableName nvarchar(256);

declare @columnName sysname;

declare @cnt bigint;

declare columnCursor cursor FOR

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS

WHERE

-- TABLE_NAME ='tablename' and

IS_NULLABLE = 'YES';

open columnCursor;

fetch next FROM columnCursor INTO @tableSchema, @tableName, @columnName;

while @@FETCH_STATUS = 0

begin

-- use dynamic sql to get count of records where column is not null

SET @sql = 'select @cnt = COUNT(*) from [' + @tableSchema + '].[' + @tableName +

'] where [' + @columnName + '] is not null';

print @sql; --uncomment for debugging

exec sp_executesql @sql, N'@cnt bigint output', @cnt = @cnt output;

INSERT INTO @tempTable SELECT @tableSchema, @tableName, @columnName, @cnt;

fetch next FROM columnCursor INTO @tableSchema, @tableName, @columnName;

end

close columnCursor;

deallocate columnCursor;

SELECT * FROM @tempTable WHERE NotNullCnt = 0;

Tábla méretek , használt tartalommal:

-- table sizes , tábla méretek egy adatbázisban
SELECT 
    @@SERVERNAME  AS servername,
    DB_NAME ( )  AS dbname,
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
	,format(getdate(), 'yyyy-MM-dd') as Datum
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
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    TotalSpaceMB desc , UsedSpaceKB desc, TableName desc

-- table sizes , tábla méretek egy adatbázisban

SELECT

@@SERVERNAME AS servername,

DB_NAME ( ) AS dbname,

t.NAME AS TableName,

s.Name AS SchemaName,

p.rows AS RowCounts,

SUM(a.total_pages) * 8 AS TotalSpaceKB,

CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,

SUM(a.used_pages) * 8 AS UsedSpaceKB,

CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,

(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,

CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB

,format(getdate(), 'yyyy-MM-dd') as Datum

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

LEFT OUTER JOIN

sys.schemas s ON t.schema_id = s.schema_id

WHERE

t.NAME NOT LIKE 'dt%'

AND t.is_ms_shipped = 0

AND i.OBJECT_ID > 255

GROUP BY

t.Name, s.Name, p.Rows

ORDER BY

TotalSpaceMB desc , UsedSpaceKB desc, TableName desc

Month: November 2018

Nem használt oszlopok – Empty columns

Table size