TSQL editor for MSSQL SERVER DBMS

Editor TSQL for subd MS SQL Server allows you to quickly and conveniently work with databases. Create them, maintain them and, if necessary, restore them. The editor supports working with server versions MS SQL 2000 and older

Create a new sheet

Creating a new sheet

Press the button "New list MS SQL" in the editor window
File -> New -> MSSQL Server
Keyboard shortcut: Ctrl + F5

Менеджер соединений с сервером MS SQL

MS SQL Server Connection Manager

CerebroSQL - connection manager for mssql

Group filter

Filter to display a list of saved connections of a specific group

Connecting to MS SQL Server

Alias is synonymous with connections
Group name - the name of the group in the list of connections
Server name - DNS name or IP address of the server
Port - the port on which MSSQL is running (by default 1433)
Database - the name of the database (by default, the user's database)
Authentication type - method of authentication in the database
- Windows authentication - OS username based authentication
- MS SQL authentication - authentication based on the entered username and password

If the authentication method is "MS SQL authentication"

User name - username in the database
Password - user password

Connect to Azure Server

Alias is synonymous with connections
Group name - the name of the group in the list of connections
Server name - DNS name or IP address of the server
Port - the port on which MSSQL is running (by default 1433)
Database - the name of the database (by default, the user's database)
Authentication type - the authentication method in the database
- MS SQL authentication - authentication based on the entered username and password
- AZURE active directory - login and password based authentication in Azure domain
Encrypt traffic -> Yes - encrypt traffic

If the authentication method is "MS SQL authentication"

User name - username in the database
Password - user password

If the authentication method is "AZURE active directory"

User name - login for the azure domain in the format login@domain
Password - password
Provider - the name of the connection provider (ODBC Driver 17 for SQL Server, ODBC Driver 16 for SQL Server, ...)

After connecting to the DBMS, the change of the current database is performed, among other things, through the drop-down list "current database"

To disconnect from the database all connections of the current sheet, click on the button "Disconnect"

Дерево объектов

Object tree

CerebroSQL - tree object (MS SQL Server)

Object tree is an interface for visualizing the structure of a cluster, managing objects.

The root record of the tree structurally consists of the name of the server to which the leaf is connected and its version.

In the meantime, there is no need to worry about it. ”

-----

Each sheet creates several connections to the database:

connection for displaying information in the object tree
connection for executing SQL queries, commands (main process)
connection for executing SQL queries, commands (additional process)

Database

List of databases created on the server MS SQL Server

select d.name, lower(d.state_desc) "state_desc"
from sys.databases d with (nolock)
order by d.database_id

List schema

SELECT s.name AS schema_name
FROM tdb.sys.schemas s
where schema_id <10000
ORDER BY s.name

Table

TABLE

List of tables in the database grouped by schema

select s.name "Schemaname",
t.name "Tablename",
t.object_id
from $$DBNAME.sys.tables t,
$$DBNAME.sys.schemas s
where t.schema_id = s.schema_id
and s.name = $$SCHEMANAM

Menu:

Edit data table (show all) - open the table in record editing mode. A data editor panel is added to the grid. The table opens completely but the first 50 rows are displayed in the grid.
Show data (top 100) - display the first 100 records in the table in the grid
Get source - generate and add to the editor the code for creating a table and its indexes
Script-select - generate and add to the editor the query text for selecting data from the table.
Script-insert - generate and add to the editor the text of the insert command to insert data from the table.
Export data to CSV - start the wizard for unloading data from the table
Import data from CSV - run the wizard for importing data from a CSV file into the selected table

COLUMNS

COLUMN node

List of table columns with data type

select c.name "ColumnName",
t.name "CoolumnType",
c.max_length,
c.precision,
c.scale
from $$DBNAME.sys.columns c,
$$DBNAME.sys.types t
where object_id =$$OBJECT_ID
and c.user_type_id = t.user_type_id
order by c.column_id

INDEXES

INDEX node

List of indexes based on table columns

List of indexes with detailed information

SELECT
Object_id = ind.object_id,
TableName = t.name,
IndexName = ind.name,
IndexId = ind.index_id,
ind.type_desc,
ind.is_unique,
ind.ignore_dup_key,
ind.is_primary_key,
ind.is_unique_constraint,
ind.fill_factor,
ind.is_disabled,
ind.is_ignored_in_optimization,
ind.compression_delay
FROM
tdb.sys.indexes ind INNER JOIN tdb.sys.tables t ON ind.object_id = t.object_id
inner join tdb.sys.schemas sch on sch.schema_id = t.schema_id
where sch.name = $$SCHEMA_NAME
and t.name =$$TABLE_NAME
and ind.name is not null

List of columns on which the index is built

SELECT col.name
FROM tdb.sys.indexes ind
INNER JOIN tdb.sys.index_columns ic ON ind.object_id = ic.object_id and ind.index_id = ic.index_id
INNER JOIN tdb.sys.tables t ON ind.object_id = t.object_id
INNER JOIN tdb.sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id
where ind.name = $$INDEX_NAME
and t.name = $$TABLE_NAME
order by ic.index_column_id

CONSTRAINTS

CONSTRAINTS Node

Table Constraint List

select table_view,
object_type,
constraint_type,
constraint_name,
details
from (
select schema_name(t.schema_id) + '.' + t.[name] as table_view,
case when t.[type] = 'U' then 'Table'
when t.[type] = 'V' then 'View'
end as [object_type],
case when c.[type] = 'PK' then 'Primary key'
when c.[type] = 'UQ' then 'Unique constraint'
when i.[type] = 1 then 'Unique clustered index'
when i.type = 2 then 'Unique index'
end as constraint_type,
isnull(c.[name], i.[name]) as constraint_name,
substring(column_names, 1, len(column_names)-1) as [details]
from $$DBNAME.sys.objects t
left outer join $$DBNAME.sys.indexes i
on t.object_id = i.object_id
left outer join $$DBNAME.sys.key_constraints c
on i.object_id = c.parent_object_id
and i.index_id = c.unique_index_id
cross apply (select col.[name] + ', '
from $$DBNAME.sys.index_columns ic
inner join $$DBNAME.sys.columns col
on ic.object_id = col.object_id
and ic.column_id = col.column_id
where ic.object_id = t.object_id
and ic.index_id = i.index_id
order by col.column_id
for xml path ('') ) D (column_names)
where is_unique = 1
and t.is_ms_shipped <> 1
union all
select schema_name(fk_tab.schema_id) + '.' + fk_tab.name as foreign_table,
'Table',
'Foreign key',
fk.name as fk_constraint_name,
schema_name(pk_tab.schema_id) + '.' + pk_tab.name
from $$DBNAME.sys.foreign_keys fk
inner join $$DBNAME.sys.tables fk_tab
on fk_tab.object_id = fk.parent_object_id
inner join $$DBNAME.sys.tables pk_tab
on pk_tab.object_id = fk.referenced_object_id
inner join $$DBNAME.sys.foreign_key_columns fk_cols
on fk_cols.constraint_object_id = fk.object_id
union all
select schema_name(t.schema_id) + '.' + t.[name],
'Table',
'Check constraint',
con.[name] as constraint_name,
con.[definition]
from $$DBNAME.sys.check_constraints con
left outer join $$DBNAME.sys.objects t
on con.parent_object_id = t.object_id
left outer join $$DBNAME.sys.all_columns col
on con.parent_column_id = col.column_id
and con.parent_object_id = col.object_id
union all
select schema_name(t.schema_id) + '.' + t.[name],
'Table',
'Default constraint',
con.[name],
col.[name] + ' = ' + con.[definition]
from $$DBNAME.sys.default_constraints con
left outer join $$DBNAME.sys.objects t
on con.parent_object_id = t.object_id
left outer join $$DBNAME.sys.all_columns col
on con.parent_column_id = col.column_id
and con.parent_object_id = col.object_id) a
where a.table_view = $$TABLE_SCHEMA.$$TABLE_NAME
order by table_view, constraint_type, constraint_name

TRIGGERS Node

TRIGGERS

List of triggers on the table

select tr.name,
tr.object_id,
tr.create_date,
tr.is_ms_shipped,
tr.is_disabled,
tr.is_not_for_replication,
t.name "table_name",
sc.name "schema_name"
from $$DBNAME.sys.triggers tr,
$$DBNAME.sys.tables t,
$$DBNAME.sys.schemas sc
where tr.parent_id = t.object_id
and t.schema_id = sc.schema_id
and tr.parent_id = $$OBJECT_ID
order by sc.name, t.name, tr.name

SIZE

Node SIZE

Information about the current size of the table and its indexes, free space

SELECT
t.Name AS TableName,
s.Name AS SchemaName,
p.Rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM
$$DBNAME.sys.tables t
INNER JOIN $$DBNAME.sys.indexes i ON t.object_id = i.object_id
INNER JOIN $$DBNAME.sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN $$DBNAME.sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN $$DBNAME.sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.Name = $$TABLE_NAME
and S.name = $$SCHEMA_NAME
AND t.is_ms_shipped = 0
AND i.object_id > 255
GROUP BY
t.Name, s.Name, p.Rows

VIEW

VIEW node

List of views in the selected database grouped by schema

SELECT av.name
FROM $$DBNAME.sys.all_views av,
$$DBNAME.sys.schemas s
where s.schema_id=av.schema_id
and s.name = $$SCHEMA
order by 1

SYNONYM node

[CerebroSQL] Synonym list in database MSSQL

List of synonyms in the selected database grouped by schemas

select s.name,
s.object_id,
s.create_date,
s.modify_date,
s.is_ms_shipped,
s.is_published,
s.base_object_name
from $$DBNAME.sys.synonyms s,
$$DBNAME.sys.schemas sc
where s.schema_id = sc.schema_id
and sc.name = $$SCHEMA_NAME

PROCEDURE node

procedure

List of procedures in the selected database grouped by schemas

select o.name "name", o.object_id
from $$DBNAME.sys.all_objects o,
$$DBNAME.sys.schemas s
where o.type in ('P','PC')
and o.schema_id = s.schema_id
and lower(s.name) = lower($$SCHEMA)
order by 1

FUNCTION node

[CerebroSQL] Function list in database MSSQL

List of functions in the selected database grouped by schemas

select o.name "name", o.object_id
from tdb.sys.all_objects o,
tdb.sys.schemas s
where o.type in ('AF','FN','FS','FT','IF')
and o.schema_id = s.schema_id
and lower(s.name) = lower($$SCHEMA_NAME)
order by 1

Menu

Get source - generate and add to the editor the text of the procedure creation command

use $$DBNAME
go
SELECT OBJECT_DEFINITION (OBJECT_ID(N'$$SCHEMA_NAME.$$PROCEDURE_NAME')) AS [Definition]

trigger

TRIGGER node

List of triggers in the selected database, grouped by tables in schemas

select tr.name,
tr.object_id,
tr.create_date,
tr.is_ms_shipped,
tr.is_disabled,
tr.is_not_for_replication,
t.name "table_name",
sc.name "schema_name"
from $$DBNAME.sys.triggers tr,
$$DBNAME.sys.tables t,
$$DBNAME.sys.schemas sc
where tr.parent_id = t.object_id
and t.schema_id = sc.schema_id
order by sc.name, t.name, tr.name

Node TYPE

[CerebroSQL] Type list in database MSSQL

List of types created in the selected database, grouped by schemas

select t.name
from $$DBNAME.sys.types t,
$$DBNAME.sys.schemas s
where s.schema_id = t.schema_id
and s.name = $$SCHEMA_NAME
order by t.name

sequence

SEQUENCE node

List of sequences created in the selected database, grouped by schema

select s.name,
s.object_id,
s.create_date,
s.modify_date,
s.start_value,
s.current_value,
s.maximum_value
from $$DBNAME.sys.sequences s,
$$DBNAME.sys.schemas sc
where sc.schema_id = s.schema_id
and sc.name = $$SCHEMA_NAME
order by s.name

SECURITY node

security

List of roles and users created on the server MSSQL

select createdate,
updatedate,
name,
dbname,
language,
denylogin,
hasaccess,
case when isntname = 1
then 'USER'
else 'SYSTEM' end "isntname",
case when isntgroup = 1
then 'GROUP'
else 'USER' end "isntgroup",
sysadmin,
securityadmin,
serveradmin,
setupadmin,
processadmin,
diskadmin,
dbcreator,
bulkadmin
from master.sys.syslogins
order by name desc

monitor

MONITOR

A single center for managing, monitoring and optimizing databases MS SQL Server

session list

SESSION LIST

List of sessions on the server MSSQL

The list of sessions is updated manually by pressing the "Refresh list session" or by pressing the key F5

exec sp_who2

When a line is selected in the list of sessions, in the block Details displays detailed information about the session, in the field Query the text of the current SQL query

SELECT (SELECT [text]
FROM sys.dm_exec_sql_text(sql_handle)) AS SqlCommand,
spid AS [Process ID], status AS [Status],
hostname AS [Host Name],
hostprocess AS [Host Process],
SPACE(3) AS [Company],
0 AS [Task],
SPACE(64) AS [Description],
loginame AS [User],
open_tran AS [Open Trans],
cmd AS [Command],
blocked AS [Blocked],
CONVERT(VARCHAR(19), waittime) AS [Wait Time],
[Waiting] = Case waittype
WHEN 0x0000 THEN SPACE(256)
Else waitresource END,
login_time AS [Login Time],
SPACE(64) AS [WTS Client], SPACE(12) AS [WTS ID],
program_name AS [Application]
FROM sys.sysprocesses WITH (NOLOCK)
WHERE spid = $$SESSION_ID

When switching to a tab PLAN displays query execution plan

Missing index

MISSING INDEX

List of indexes needed to optimize the performance of queries (improving the performance of the database).

Updating the list of recommended indexes is carried out by pressing the button F5

[CerebroSQL] MSSQL Monitor - missing index

SELECT TOP 100
dm_mid.database_id AS DatabaseID,
dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
dm_migs.last_user_seek AS Last_User_Seek,
OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','')
+ CASE
WHEN dm_mid.equality_columns IS NOT NULL
AND dm_mid.inequality_columns IS NOT NULL THEN '_'
ELSE ''
END
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
+ ']'
+ ' ON ' + dm_mid.statement
+ ' (' + ISNULL (dm_mid.equality_columns,'')
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns
IS NOT NULL THEN ',' ELSE '' END
+ ISNULL (dm_mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid
ON dm_mig.index_handle = dm_mid.index_handle
ORDER BY Avg_Estimated_Impact DESC

wait stats

WAIT STATS

Statistics on session waits in databases MSSQL sorted by frequency of occurrence

SELECT TOP 30
[Wait_type] = wait_type,
[Wait_time] = wait_time_ms / 1000,
[waiting] = CONVERT(DECIMAL(12,2), wait_time_ms * 100.0
/ SUM(wait_time_ms) OVER())
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE '%SLEEP%'
ORDER BY wait_time_ms DESC

Edit configuration

EDIT CONFIGURATION

Server Settings Editor MS SQL

[CerebroSQL] MSSQL Monitor - edit configure

select s.configuration_id,
s.name,
s.value,
s.minimum,
s.maximum,
s.value_in_use,
s.description,
s.is_dynamic,
s.is_advanced
from master.sys.configurations s
order by name

Changing the value of a parameter

Select base from the list database name
Listed Parameter list select the desired configuration option
In field new value enter new value
Push button Apply

Server logs

SERVER LOG

Viewing work logs MS SQL Server

TSQL editor for MSSQL SERVER DBMS

Menu

Create a new sheet

Creating a new sheet

​

MS SQL Server Connection Manager

Connecting to MS SQL Server

Connect to Azure Server

Object tree

Database

TABLE

COLUMN node

INDEX node

CONSTRAINTS Node

TRIGGERS Node

Node SIZE

VIEW node

Menu

SYNONYM node

PROCEDURE node

Menu

FUNCTION node

TRIGGER node

Menu

Node TYPE

SEQUENCE node

SECURITY node

MONITOR

SESSION LIST

MISSING INDEX

WAIT STATS

EDIT CONFIGURATION

Changing the value of a parameter

SERVER LOG