# SQL Script Kiểm tra

## 0. KIỂM TRA TRẠNG THÁI DATABASES

```sql
SELECT * FROM sys.databases 
```

## 1.ĐƯỜNG DẪN THƯ MỤC FILE DATABASE

{% code fullWidth="false" %}

```sql
SELECT 

    mdf.database_id, 

    mdf.name, 

    mdf.physical_name as data_file, 

    ldf.physical_name as log_file, 

    db_size = CAST((mdf.size * 8.0)/1024 AS DECIMAL(8,2)), 

    log_size = CAST((ldf.size * 8.0 / 1024) AS DECIMAL(8,2))

FROM (SELECT * FROM sys.master_files WHERE type_desc = 'ROWS' ) mdf

JOIN (SELECT * FROM sys.master_files WHERE type_desc = 'LOG' ) ldf

ON mdf.database_id = ldf.database_id
```

{% endcode %}

## 2.KIỂM TRA DUNG LƯỢNG CÁC TABLE TRONG DATABASE

```sql
USE [YourDBName] -- Thay đổi tên Database cần kiểm tra
GO
SELECT
s.Name AS SchemaName,
t.Name AS TableName,
p.rows AS RowCounts,
CAST(ROUND((SUM(a.used_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Used_MB,
CAST(ROUND((SUM(a.total_pages) - SUM(a.used_pages)) / 128.00, 2) AS NUMERIC(36, 2)) AS Unused_MB,
CAST(ROUND((SUM(a.total_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Total_MB
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
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
GROUP BY t.Name, s.Name, p.Rows
ORDER BY s.Name, t.Name
GO
```

## 3.KIỂM TRA CÁC KẾT NỐI TỚI DATABASE

```sql

-- This script returns the Database, Number of open connections and logged-in user credentials

SELECT DB_NAME(dbid) as DBName, COUNT(dbid) as NumberOfConnections,loginame as LoginName
FROM sys.sysprocesses
WHERE dbid > 0
GROUP BY dbid, loginame

-- This script returns the status, login name and host name for the database you specify.

SELECT spid, status, loginame, hostname, blocked, db_name(dbid), cmd
FROM sys.sysprocesses
WHERE db_name(dbid) = 'databasename'



-- SQL Server 2005 introduced Dynamic Management Views, in this case we can just check if the is_user_process is 1 instead of checking for the value of spid.
-- This will work in SQL Server 2005 and 2008.

SELECT count(*) as ConnectionCount
FROM sys.dm_exec_sessions
WHERE is_user_process = 1


--If you want both system and user information:

SELECT count(*) as ConnectionCount,
CASE when is_user_process =1 then 'UserProcess' else 'System Process' end
FROM sys.dm_exec_sessions
GROUP by is_user_process


--Also, can look at the sys.dm_exec_connections Dynamic Management Views. This view only shows user connections.

SELECT  count(*)  
FROM sys.dm_exec_connections

```

## 4.ĐÓNG KẾT NỐI ỨNG DỤNG TỚI DATABASE

```sql
USE master;
DECLARE @kill varchar(8000);
SET @kill = ''; 
 
SELECT @kill = @kill 'kill ' CONVERT(varchar(5), spid) ';' 
FROM master..sysprocesses 
WHERE dbid = db_id('Tên database') --db_id('Tên database') lấy id của process database cần kill
 
EXEC(@kill);  --thực thi câu sql động -> kill id
 
--- Sau khi kill connection ta se drop được database dễ dàng
DROP DATABASE [Tên database]
GO
```

## 5.KIỂM TRA NGÀY TẠO DATABASE

```sql
--Cách 1:
EXEC sp_helpdb 'DTECH'
GO
--Cách 2:
SELECT      create_date
FROM        sys.databases
WHERE       name = 'DTECH'
GO
-- Hoặc 2.1
SELECT      create_date,name
FROM        sys.databases
WHERE       year(create_date)=2023
order by create_date desc

--Cách 3:
DBCC TRACEON(3604)
GO
DBCC PAGE('SqlAndMe', 1, 9, 3)
GO
```

## 6.KIỂM TRA NGÀY CUỐI CÙNG TÁC ĐỘNG VÀO DATABASE

```sql
--Nhìn qua ngày thay đổi cuối cùng của Table
SELECT name [TableName],
       Create_date [CreateDate],
       modify_date [LastUpdate]
FROM sys.all_objects
WHERE type = 'U'
ORDER BY modify_date DESC;
```

## 7.THÔNG TIN DATABASE NGUỒN ĐÍCH BACKUP RESTORE

```sql
SELECT 
   [rs].[destination_database_name], -- Database đích
   [bs].[database_name] as [source_database_name], --Database nguồn
   [rs].[restore_date],  -- Thời gian khôi phục
   [bs].[backup_start_date], -- Thời gian bắt đầu sao lưu
   [bs].[backup_finish_date], -- Thời gian kết thúc sao lưu
   [bmf].[physical_device_name] as [backup_file_used_for_restore] -- Đường dẫn tên tệp sao lưu
FROM msdb..restorehistory rs
INNER JOIN msdb..backupset bs ON [rs].[backup_set_id] = [bs].[backup_set_id]
INNER JOIN msdb..backupmediafamily bmf ON [bs].[media_set_id] = [bmf].[media_set_id] 
```

## 8.KIỂM TRA NGÀY TẠO, NGÀY SỬA CUỐI CÙNG VÀO TABLE

<pre class="language-sql"><code class="lang-sql"><strong>Use DTECH
</strong><strong>GO
</strong><strong>SELECT
</strong>        [name]
       ,create_date
       ,modify_date
FROM
        sys.tables
</code></pre>

## 9.KIỂM TRA SO SÁNH CẤU TRÚC TABLE Ở 2 DATABASE

```sql
-- Thay thế các giá trị trong ngoặc vuông [] bằng tên thực tế
DECLARE @TableName NVARCHAR(128) = 'DuLieuKeToan';
DECLARE @SourceDB NVARCHAR(128) = 'DB_NGUON'; -- <== Tên Database nguồn
DECLARE @DestDB NVARCHAR(128) = 'DB_DICH';   -- <== Tên Database đích

-- Xây dựng các câu lệnh SQL động
DECLARE @SourceSQL NVARCHAR(MAX);
DECLARE @DestSQL NVARCHAR(MAX);

SET @SourceSQL = N'
SELECT 
    COLUMN_NAME, 
    DATA_TYPE, 
    CHARACTER_MAXIMUM_LENGTH, 
    NUMERIC_PRECISION, 
    NUMERIC_SCALE
FROM [' + @SourceDB + '].INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName';

SET @DestSQL = N'
SELECT 
    COLUMN_NAME, 
    DATA_TYPE, 
    CHARACTER_MAXIMUM_LENGTH, 
    NUMERIC_PRECISION, 
    NUMERIC_SCALE
FROM [' + @DestDB + '].INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName';

-- Tạo bảng tạm để lưu kết quả
IF OBJECT_ID('tempdb..#SourceSchema') IS NOT NULL DROP TABLE #SourceSchema;
CREATE TABLE #SourceSchema (COLUMN_NAME NVARCHAR(128), DATA_TYPE NVARCHAR(128), CHARACTER_MAXIMUM_LENGTH INT, NUMERIC_PRECISION TINYINT, NUMERIC_SCALE INT);
INSERT INTO #SourceSchema EXEC sp_executesql @SourceSQL, N'@TableName NVARCHAR(128)', @TableName;

IF OBJECT_ID('tempdb..#DestSchema') IS NOT NULL DROP TABLE #DestSchema;
CREATE TABLE #DestSchema (COLUMN_NAME NVARCHAR(128), DATA_TYPE NVARCHAR(128), CHARACTER_MAXIMUM_LENGTH INT, NUMERIC_PRECISION TINYINT, NUMERIC_SCALE INT);
INSERT INTO #DestSchema EXEC sp_executesql @DestSQL, N'@TableName NVARCHAR(128)', @TableName;


-- So sánh và hiển thị kết quả
SELECT 
    COALESCE(nguon.COLUMN_NAME, dich.COLUMN_NAME) AS TenCot,
    nguon.DATA_TYPE AS KieuDuLieu_Nguon,
    dich.DATA_TYPE AS KieuDuLieu_Dich,
    nguon.CHARACTER_MAXIMUM_LENGTH AS DoRong_Nguon,
    dich.CHARACTER_MAXIMUM_LENGTH AS DoRong_Dich,
    nguon.NUMERIC_PRECISION AS DoChinhXac_Nguon,
    dich.NUMERIC_PRECISION AS DoChinhXac_Dich,
    nguon.NUMERIC_SCALE AS SoThapPhan_Nguon,
    dich.NUMERIC_SCALE AS SoThapPhan_Dich,
    CASE 
        WHEN dich.COLUMN_NAME IS NULL THEN N'Cột chỉ tồn tại ở NGUỒN'
        WHEN nguon.COLUMN_NAME IS NULL THEN N'Cột chỉ tồn tại ở ĐÍCH'
        WHEN nguon.DATA_TYPE <> dich.DATA_TYPE THEN N'Khác kiểu dữ liệu'
        WHEN ISNULL(nguon.CHARACTER_MAXIMUM_LENGTH, -1) <> ISNULL(dich.CHARACTER_MAXIMUM_LENGTH, -1) THEN N'Khác độ rộng chuỗi'
        WHEN ISNULL(CAST(nguon.NUMERIC_PRECISION AS INT), -1) <> ISNULL(CAST(dich.NUMERIC_PRECISION AS INT), -1) 
             OR ISNULL(nguon.NUMERIC_SCALE, -1) <> ISNULL(dich.NUMERIC_SCALE, -1) THEN N'Khác độ chính xác/thập phân số'
        ELSE ''
    END AS GhiChu
FROM #SourceSchema AS nguon
FULL OUTER JOIN #DestSchema AS dich ON nguon.COLUMN_NAME = dich.COLUMN_NAME
WHERE 
    dich.COLUMN_NAME IS NULL 
    OR nguon.COLUMN_NAME IS NULL
    OR nguon.DATA_TYPE <> dich.DATA_TYPE
    OR ISNULL(nguon.CHARACTER_MAXIMUM_LENGTH, -1) <> ISNULL(dich.CHARACTER_MAXIMUM_LENGTH, -1)
    -- SỬA LỖI Ở ĐÂY: Chuyển sang INT trước khi so sánh với -1
    OR ISNULL(CAST(nguon.NUMERIC_PRECISION AS INT), -1) <> ISNULL(CAST(dich.NUMERIC_PRECISION AS INT), -1)
    OR ISNULL(nguon.NUMERIC_SCALE, -1) <> ISNULL(dich.NUMERIC_SCALE, -1);

-- Dọn dẹp bảng tạm
DROP TABLE #SourceSchema;
DROP TABLE #DestSchema;
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://kythuat.dtechvn.com/sql-server/sql-script-kiem-tra.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.