adminbd

Записи по mssql,oracle,windows и linux

Have a Question?

If you have any question you can ask below or enter what you are looking for!

Узнать версию sql запрос в самом sql

 

   SELECT


   SERVERPROPERTY('Edition') AS 'Edition',


   SERVERPROPERTY('ProductVersion') AS 'ProductVersion',


   SERVERPROPERTY('ProductLevel') AS 'ProductLevel',


   SERVERPROPERTY('ResourceLastUpdateDateTime') AS 'ResourceLastUpdateDateTime',


   SERVERPROPERTY('ResourceVersion') AS 'ResourceVersion'


   GO

 

Как узнать размер таблиц в БД sql. Узнать размер индексов в mssql.

DBCC UPDATEUSAGE (0);
CREATE TABLE #t(
[имя таблицы] varchar(255)
,[строк] varchar(255)
,[зарезервировано] varchar(255)
,[всего данных] varchar(255)
,[размер индексов] varchar(255)
,[свободно] varchar(255)
);
 
INSERT INTO #t
exec sp_msforeachtable N'exec sp_spaceused ''?''';
 
SELECT * FROM #t ORDER BY CONVERT(bigint, REPLACE([всего данных], ' KB', '')) DESC;
 
DROP TABLE #t;
DECLARE @TableInfo TABLE (
    table_name sysname,
    row_count int,
    reserved_size_kb nvarchar(50),
    data_size_kb nvarchar(50),
    index_size_kb nvarchar(50),
    unused_size_kb nvarchar(50)
)
 
INSERT INTO @TableInfo
EXEC sp_MSforeachtable 'sp_spaceused ''?'''
 
UPDATE @TableInfo 
SET 
    data_size_kb     = replace(data_size_kb, 'KB', ''),
    reserved_size_kb = replace(reserved_size_kb, 'KB', ''),
    index_size_kb    = replace(index_size_kb, 'KB', ''),
    unused_size_kb   = replace(unused_size_kb, 'KB', '')
 
SELECT *, 
    reserved_size_kb/1024 AS reserved_size_mb, 
    data_size_kb/1024 AS 
Читать далее