Document SQL Server Table (edit)
https://docs.microsoft.com/en-us/sql/ssms/tutorials/scripting-ssms?view=sql-server-ver15
https://github.com/microsoft/mssql-scripter
https://devblogs.microsoft.com/scripting/use-powershell-to-document-your-sql-database/
Bạn muốn hiểu ý nghĩa của từng cột trong một bảng User trong cơ sở dữ liệu? Bài viết này dành cho bạn.
http://poorsql.com/ (Tool Format SQL online)
https://stackoverflow.com/questions/369266/how-to-document-a-database
Dataedo
https://dataedo.com/ (Paid)
Enterprise Architect
https://sparxsystems.com/ (Paid)
SchemaCrawler (HAY HAY HAY)
Free database schema discovery and comprehension tool (command line tool)
https://www.schemacrawler.com/
In my experience, ER (or UML) diagrams aren't the most useful artifact - with a large number of tables, diagrams (especially reverse engineered ones) are often a big convoluted mess that nobody learns anything from.
For my money, some good human-readable documentation (perhaps supplemented with diagrams of smaller portions of the system) will give you the most mileage. This will include, for each table:
- Descriptions of what the table means and how it's functionally used (in the UI, etc.)
- Descriptions of what each attribute means, if it isn't obvious
- Explanations of the relationships (foreign keys) from this table to others, and vice-versa
- Explanations of additional constraints and / or triggers
- Additional explanation of major views & procs that touch the table, if they're not well documented already
With all of the above, don't document for the sake of documenting - documentation that restates the obvious just gets in people's way. Instead, focus on the stuff that confused you at first, and spend a few minutes writing really clear, concise explanations. That'll help you think it through, and it'll massively help other developers who run into these tables for the first time.
As others have mentioned, there are a wide variety of tools to help you manage this, like Enterprise Architect, Red Gate SQL Doc, and the built-in tools from various vendors. But while tool support is helpful (and even critical, in bigger databases), doing the hard work of understanding and explaining the conceptual model of the database is the real win. From that perspective, you can even do it in a text file (though doing it in Wiki form would allow several people to collaborate on adding to that documentation incrementally - so, every time someone figures out something, they can add it to the growing body of documentation instantly).
T-SQL: view description of all columns in a table
-- list table columns SELECT OBJECT_NAME(c.OBJECT_ID) [TableName] ,c.name [ColumnName] ,t.name [DataType] ,x.Value [Description] ,c.max_length [Length] ,c.Precision ,c.Scale FROM sys.columns AS c JOIN sys.types AS t ON c.user_type_id = t.user_type_id LEFT JOIN sys.extended_properties x ON x.major_id = c.object_id AND x.minor_id = c.column_id WHERE OBJECT_NAME(c.OBJECT_ID) = 'User' ORDER BY c.OBJECT_ID ,c.column_id;
Description - How to Document SQL Server Tables
sp_addextendedproperty N'MS_Description', N'Thật là tuyệt vời', 'USER', N'dbo', 'TABLE', N'User', 'COLUMN', N'Username'
sp_addextendedproperty N'MS_Description', N'Thật là tuyệt vời', 'USER', N'dbo', 'TABLE', N'User', 'COLUMN', N'Username'
https://www.red-gate.com/simple-talk/sql/t-sql-programming/documenting-your-sql-server-database/
https://www.sqlshack.com/how-to-document-sql-server-database-objects/
https://www.red-gate.com/simple-talk/sysadmin/devops/how-to-document-sql-server-tables/ (HAY HAY HAY)
Source: https://github.com/Phil-Factor/CompileTableComments (HAY HAY HAY)
Articles: https://www.red-gate.com/simple-talk/author/phil-factor/ (HAY HAY HAY)
https://www.sqlshack.com/methods-to-script-sql-server-database-objects/
DBScripts (HAY HAY HAY)
https://www.databasezone.com/techdocs/dbscripts.html
/*******************************************************************************************/
-- Script name: ShowTableColumnDescriptions.sql
-- Created by: Conrad Muller March 21, 2015
-- https://www.databasezone.com/
--
-- ShowTableColumnDescriptions.sql will list all of the tables in your database
-- including each column with its data type and Description. The output is a grid
-- that you can copy and paste into a spreadsheet or drop into a Web page.
--
-- THIS CODE IS FOR EDUCATION AND IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND,
-- EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED
-- WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
/*******************************************************************************************/
SELECT sys.schemas.name AS SchemaName,
sys.tables.name AS TableName,
sys.columns.name AS ColumnName,
sys.columns.column_id AS ColumnOrder,
CASE sys.types.name
WHEN 'varchar' THEN sys.types.name + '(' + CASE sys.columns.max_length
WHEN -1 THEN 'Max'
ELSE CAST(sys.columns.max_length AS varchar(5))
END + ')'
WHEN 'varbinary' THEN sys.types.name + '(' + CASE sys.columns.max_length
WHEN -1 THEN 'Max'
ELSE CAST(sys.columns.max_length AS varchar(5))
END + ')'
WHEN 'nvarchar' THEN sys.types.name + '(' + CASE sys.columns.max_length
WHEN -1 THEN 'Max'
ELSE CAST(sys.columns.max_length AS varchar(5))
END + ')'
WHEN 'char' THEN sys.types.name + '(' + CAST(sys.columns.max_length AS varchar(5)) + ')'
WHEN 'numeric' THEN sys.types.name + '(' + CAST(sys.columns.precision AS varchar(5)) + ',' + CAST(sys.columns.scale AS varchar(5)) +')'
WHEN 'decimal' THEN sys.types.name + '(' + CAST(sys.columns.precision AS varchar(5)) + ',' + CAST(sys.columns.scale AS varchar(5)) +')'
WHEN 'money' THEN sys.types.name + '(' + CAST(sys.columns.precision AS varchar(5)) + ',' + CAST(sys.columns.scale AS varchar(5)) +')'
ELSE sys.types.name
END AS DataType,
isNull(sys.columns.collation_name, '') AS COLLATION,
CASE sys.columns.is_nullable
WHEN '1' THEN 'Nullable'
ELSE ''
END AS IsNullable,
CASE sys.columns.is_ansi_padded
WHEN '1' THEN 'ANSIPadded'
ELSE ''
END AS ANSIPadded,
sys.extended_properties.value AS ColumnDescription
FROM sys.tables
LEFT OUTER JOIN sys.columns ON sys.tables.object_id = sys.columns.object_id
LEFT OUTER JOIN sys.schemas ON sys.tables.schema_id = sys.schemas.schema_id
LEFT OUTER JOIN sys.extended_properties ON sys.extended_properties.major_id = sys.columns.object_id
AND sys.extended_properties.minor_id = sys.columns.column_id
LEFT OUTER JOIN sys.types ON sys.types.system_type_id = sys.columns.system_type_id
WHERE sys.types.name <> 'sysname'
AND (sys.extended_properties.class = 1
OR sys.extended_properties.class IS NULL)
ORDER BY sys.schemas.name,
sys.tables.name,
sys.columns.column_id
Log T-SQL before executing
https://github.com/jphellemons/CommandAsSql
https://github.com/jeroenpot/SqlHelper/
LINQ Extensions
https://www.coder.work/article/1601708
https://www.atmarkit.co.jp/fdotnet/dotnettips/986dynamiclinq/dynamiclinq.html
SQL Server Tools
- Express Profiler (here)
- SQL Server Profiler
Daily Backup Database
Today, I will make a backup manual database daily work by creating SQL Job.
First we have to install the SQL Server Agent SQL.
Set mode to run automatically when Windows startup.
Step 1. Setup SQL Server Agent + Run Sql Server Agent services
Step 2. Create database: "Demo" + Create store procedure jb_Backup_Database
-- ============================================= -- Author: Phuong Nguyen -- Create date: YYYY-MM-DD -- Description: Backup database -- ============================================= ALTER PROCEDURE [dbo].[jb_Backup_Database] AS BEGIN DECLARE @DBName NVARCHAR(50) -- database name DECLARE @BKPath NVARCHAR(256) -- path for backup files DECLARE @BKFileName NVARCHAR(256) -- filename for backup DECLARE @BKFileDate NVARCHAR(20) -- used for file name -- Set dbname is current db SET @DBName = DB_NAME() -- Database backup directory SET @BKPath = 'C:\DB\Backup\' -- Filename format SELECT @BKFileDate = CONVERT(NVARCHAR(20),getDate(),112) SET @BKFileName = @BKPath + @DBName + '_' + @BKFileDate + '.BAK' BACKUP DATABASE @DBName TO DISK = @BKFileName END
- Run proc to test result
exec jb_Backup_Database
Step 3. Create Job call stored procedure
SQL Script:
USE [msdb] GO /****** Object: Job [jb_Backup_database_demo] Script Date: 6/28/2015 4:18:14 PM ******/ BEGIN TRANSACTION DECLARE @ReturnCode INT SELECT @ReturnCode = 0 /****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 6/28/2015 4:18:14 PM ******/ IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1) BEGIN EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback END DECLARE @jobId BINARY(16) EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'jb_Backup_database_demo', @enabled=1, @notify_level_eventlog=0, @notify_level_email=0, @notify_level_netsend=0, @notify_level_page=0, @delete_level=0, @description=N'No description available.', @category_name=N'[Uncategorized (Local)]', @owner_login_name=N'sa', @job_id = @jobId OUTPUT IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback /****** Object: Step [RunBackupDemo] Script Date: 6/28/2015 4:18:15 PM ******/ EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'RunBackupDemo', @step_id=1, @cmdexec_success_code=0, @on_success_action=1, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command=N'exec jb_Backup_Database', @database_name=N'Demo', @flags=0 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Everyday', @enabled=1, @freq_type=4, @freq_interval=1, @freq_subday_type=1, @freq_subday_interval=0, @freq_relative_interval=0, @freq_recurrence_factor=0, @active_start_date=20150627, @active_end_date=99991231, @active_start_time=234700, @active_end_time=235959, @schedule_uid=N'624f7aee-128d-403b-8037-4bde1c1a792d' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback COMMIT TRANSACTION GOTO EndSave QuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION EndSave: GO
SQL Server Profiler - RPC:Completed and SQL:BatchCompleted
BatchCompleted means TSQL code (e.g. selects) have completed.
RPC:Completed means stored proc has completed.
RPC:Completed means EF executes SQL code dynamically using sp_executesql.
SP:Completed means stored proc has completed.
Tham khảo:
http://phuongnguyentrung.blogspot.com/2015/06/sql-tips-create-sql-job-to-backup.html
http://phuongnguyentrung.blogspot.com/2016/03/cach-giam-dung-luong-file-transaction.html
http://phuongnguyentrung.blogspot.com/2015/01/top-must-know-frameworks-for-net-web.html
BACK END DEVELOPER
CACHE COW
SOURCE CODE: HTTPS://GITHUB.COM/ALIOSTAD/CACHECOW
Redis
