Заметки

Блокнот/Заметки

Тонны кода. SQL

Автор: Admin Ноябрь 29, 2019 0 комментария

Информация о SQL сервере:

-- Имена сервера и экземпляра 
Select @@SERVERNAME as [Server\Instance]; 

-- версия SQL Server 
Select @@VERSION as SQLServerVersion; 

-- экземпляр SQL Server 
Select @@ServiceName AS ServiceInstance;

 -- Текущая БД (БД, в контексте которой выполняется запрос)
Select DB_NAME() AS CurrentDB_Name;

-- Имена сервера и экземпляра

Select @@SERVERNAME as [Server\Instance];

-- версия SQL Server

Select @@VERSION as SQLServerVersion;

-- экземпляр SQL Server

Select @@ServiceName AS ServiceInstance;

-- Текущая БД (БД, в контексте которой выполняется запрос)

Select DB_NAME() AS CurrentDB_Name;

Время работы с последнего старта:

SELECT  @@Servername AS ServerName ,
        create_date AS  ServerStarted ,
        DATEDIFF(s, create_date, GETDATE()) / 86400.0 AS DaysRunning ,
        DATEDIFF(s, create_date, GETDATE()) AS SecondsRunnig
FROM    sys.databases
WHERE   name = 'tempdb';

SELECT @@Servername AS ServerName ,

create_date AS ServerStarted ,

DATEDIFF(s, create_date, GETDATE()) / 86400.0 AS DaysRunning ,

DATEDIFF(s, create_date, GETDATE()) AS SecondsRunnig

FROM sys.databases

WHERE name = 'tempdb';

Общая информация об активных подключениях:

SELECT  @@Servername AS Server ,
        DB_NAME(database_id) AS DatabaseName ,
        COUNT(database_id) AS Connections ,
        Login_name AS  LoginName ,
        MIN(Login_Time) AS Login_Time ,
        MIN(COALESCE(last_request_end_time, last_request_start_time))
                                                         AS  Last_Batch
FROM    sys.dm_exec_sessions
WHERE   database_id > 0
        AND DB_NAME(database_id) NOT IN ( 'master', 'msdb' )
GROUP BY database_id ,
         login_name
ORDER BY DatabaseName;

SELECT @@Servername AS Server ,

DB_NAME(database_id) AS DatabaseName ,

COUNT(database_id) AS Connections ,

Login_name AS LoginName ,

MIN(Login_Time) AS Login_Time ,

MIN(COALESCE(last_request_end_time, last_request_start_time))

AS Last_Batch

FROM sys.dm_exec_sessions

WHERE database_id > 0

AND DB_NAME(database_id) NOT IN ( 'master', 'msdb' )

GROUP BY database_id ,

login_name

ORDER BY DatabaseName;

Дата последних бэкапов:

SELECT  @@Servername AS ServerName ,
        d.Name AS DBName ,
        MAX(b.backup_finish_date) AS LastBackupCompleted
FROM    sys.databases d
        LEFT OUTER JOIN msdb..backupset b
                    ON b.database_name = d.name
                       AND b.[type] = 'D'
GROUP BY d.Name
ORDER BY d.Name;

SELECT @@Servername AS ServerName ,

d.Name AS DBName ,

MAX(b.backup_finish_date) AS LastBackupCompleted

FROM sys.databases d

LEFT OUTER JOIN msdb..backupset b

ON b.database_name = d.name

AND b.[type] = 'D'

GROUP BY d.Name

ORDER BY d.Name;

Каталоги последних бэкапов:

SELECT  @@Servername AS ServerName ,
        d.Name AS DBName ,
        b.Backup_finish_date ,
        bmf.Physical_Device_name
FROM    sys.databases d
        INNER JOIN msdb..backupset b ON b.database_name = d.name
                                        AND b.[type] = 'D'
        INNER JOIN msdb.dbo.backupmediafamily bmf ON b.media_set_id = bmf.media_set_id
ORDER BY d.NAME ,
        b.Backup_finish_date DESC;

SELECT @@Servername AS ServerName ,

d.Name AS DBName ,

b.Backup_finish_date ,

bmf.Physical_Device_name

FROM sys.databases d

INNER JOIN msdb..backupset b ON b.database_name = d.name

AND b.[type] = 'D'

INNER JOIN msdb.dbo.backupmediafamily bmf ON b.media_set_id = bmf.media_set_id

ORDER BY d.NAME ,

b.Backup_finish_date DESC;

Список всех баз:

/*1*/
EXEC sp_helpdb; 

/*2*/
EXEC sp_Databases; 

/*3*/
SELECT  @@SERVERNAME AS Server ,
        name AS DBName ,
        recovery_model_Desc AS RecoveryModel ,
        Compatibility_level AS CompatiblityLevel ,
        create_date ,
        state_desc
FROM    sys.databases
ORDER BY Name; 

/*4*/

SELECT  @@SERVERNAME AS Server ,
        d.name AS DBName ,
        create_date ,
        compatibility_level ,
        m.physical_name AS FileName
FROM    sys.databases d
        JOIN sys.master_files m ON d.database_id = m.database_id
WHERE   m.[type] = 0 -- data files only
ORDER BY d.name;

/*1*/

EXEC sp_helpdb;

/*2*/

EXEC sp_Databases;

/*3*/

SELECT @@SERVERNAME AS Server ,

name AS DBName ,

recovery_model_Desc AS RecoveryModel ,

Compatibility_level AS CompatiblityLevel ,

create_date ,

state_desc

FROM sys.databases

ORDER BY Name;

/*4*/

SELECT @@SERVERNAME AS Server ,

d.name AS DBName ,

create_date ,

compatibility_level ,

m.physical_name AS FileName

FROM sys.databases d

JOIN sys.master_files m ON d.database_id = m.database_id

WHERE m.[type] = 0 -- data files only

ORDER BY d.name;

Размеры баз:

with fs
as
(
    select database_id, type, size * 8.0 / 1024 size
    from sys.master_files
)
select 
    name,
    (select sum(size) from fs where type = 0 and fs.database_id = db.database_id) DataFileSizeMB,
    (select sum(size) from fs where type = 1 and fs.database_id = db.database_id) LogFileSizeMB
from sys.databases db

/*После того, как стало известно о размере баз данных, можно посмотреть сколько место фактически используется.*/

SELECT SUM(unallocated_extent_page_count) AS [free pages],
    (SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM sys.dm_db_file_space_usage;

with fs

(

select database_id, type, size * 8.0 / 1024 size

from sys.master_files

)

select

name,

(select sum(size) from fs where type = 0 and fs.database_id = db.database_id) DataFileSizeMB,

(select sum(size) from fs where type = 1 and fs.database_id = db.database_id) LogFileSizeMB

from sys.databases db

/*После того, как стало известно о размере баз данных, можно посмотреть сколько место фактически используется.*/

SELECT SUM(unallocated_extent_page_count) AS [free pages],

(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]

FROM sys.dm_db_file_space_usage;

Расположение файлов базы данных:

Use [ИмяБазы]

/*1*/
EXEC sp_Helpfile; 

/*2*/

SELECT  @@Servername AS Server ,
        DB_NAME() AS DB_Name ,
        File_id ,
        Type_desc ,
        Name ,
        LEFT(Physical_Name, 1) AS Drive ,
        Physical_Name ,
        RIGHT(physical_name, 3) AS Ext ,
        Size ,
        Growth
FROM    sys.database_files
ORDER BY File_id;

Use [ИмяБазы]

/*1*/

EXEC sp_Helpfile;

/*2*/

SELECT @@Servername AS Server ,

DB_NAME() AS DB_Name ,

File_id ,

Type_desc ,

Name ,

LEFT(Physical_Name, 1) AS Drive ,

Physical_Name ,

RIGHT(physical_name, 3) AS Ext ,

Size ,

Growth

FROM sys.database_files

ORDER BY File_id;

Размеры таблиц:

Use [ИмяБазы]

SELECT
	a3.name AS [schemaname],
	a2.name AS [tablename],
	a1.rows as row_count,
	(a1.reserved + ISNULL(a4.reserved,0))* 8 AS [reserved], 
	a1.data * 8 AS [data],
	(CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS [index_size],
	(CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS [unused]
FROM
	(SELECT 
		ps.object_id,
		SUM (
			CASE
				WHEN (ps.index_id < 2) THEN row_count
				ELSE 0
			END
			) AS [rows],
		SUM (ps.reserved_page_count) AS reserved,
		SUM (
			CASE
				WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
				ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
			END
			) AS data,
		SUM (ps.used_page_count) AS used
	FROM sys.dm_db_partition_stats ps
	GROUP BY ps.object_id) AS a1
LEFT OUTER JOIN 
	(SELECT 
		it.parent_id,
		SUM(ps.reserved_page_count) AS reserved,
		SUM(ps.used_page_count) AS used
	 FROM sys.dm_db_partition_stats ps
	 INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
	 WHERE it.internal_type IN (202,204)
	 GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)
INNER JOIN sys.all_objects a2  ON ( a1.object_id = a2.object_id ) 
INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
WHERE a2.type <> N'S' and a2.type <> N'IT'
ORDER BY reserved DESC

Use [ИмяБазы]

SELECT

a3.name AS [schemaname],

a2.name AS [tablename],

a1.rows as row_count,

(a1.reserved + ISNULL(a4.reserved,0))* 8 AS [reserved],

a1.data * 8 AS [data],

(CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS [index_size],

(CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS [unused]

FROM

(SELECT

ps.object_id,

SUM (

CASE

WHEN (ps.index_id < 2) THEN row_count

ELSE 0

END

) AS [rows],

SUM (ps.reserved_page_count) AS reserved,

SUM (

CASE

WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)

ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)

END

) AS data,

SUM (ps.used_page_count) AS used

FROM sys.dm_db_partition_stats ps

GROUP BY ps.object_id) AS a1

LEFT OUTER JOIN

(SELECT

it.parent_id,

SUM(ps.reserved_page_count) AS reserved,

SUM(ps.used_page_count) AS used

FROM sys.dm_db_partition_stats ps

INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)

WHERE it.internal_type IN (202,204)

GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)

INNER JOIN sys.all_objects a2 ON ( a1.object_id = a2.object_id )

INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)

WHERE a2.type <> N'S' and a2.type <> N'IT'

ORDER BY reserved DESC

…
ДАЛЕЕ

Заметки, Популярное

1с параметры запуска

Автор: Admin Ноябрь 11, 2019 0 комментария

Про параметры запуска 1с написано уже много статей, да и на самом сайта 1с так же можно найти полную информацию. Поэтому подробно расписывать все нет смысла. Но для себя решил сделать отдельную заметку по параметрам запуска 1с. Итак, берем хорошую…
ДАЛЕЕ

Администрирование, Заметки, Популярное, Прочее

Администрирование, прочее

Вид операций в банковской выписке

Автор: Admin Ноябрь 7, 2019 0 комментария

Попросили меня на днях подсказать, как программа при загрузке банковских выписок определяет вид операции ‘КомиссияБанка’. Я сказал: дайте мне 10 минут и я все выясню! Но только спустя пару часов я смогу разобраться в этом вопросе. Даже поиск в интернете…
ДАЛЕЕ

всякое разное, Заметки, Интересное, Популярное, Программирование, Прочее

Интересное, Общие, программирование

Стандартные обработки 1с

Автор: Admin Октябрь 30, 2019 0 комментария

Понадобилась мне на днях одна стандартная обработка “Поиск ссылок на объект” для управляемых форм. В моей конфигурации такой по умолчанию не было и я полез искать её в интернет. И совершенно случайно наткнулся на гениальное решение от 1с – набор…
ДАЛЕЕ

Администрирование, всякое разное, Заметки, Общие, Популярное, Программирование, Прочее

заметки, Обработки, Популярное