Топ-100
 
CerebroSQL

TSQL Editor for MSSQL SERVER DBMS

Didn't find the features you want? Write in the comments on the forum   and we will add the required functionality
Creating a new sheet
  1. Press the button "New list MS SQL" in the editor window

  2. File -> New -> MSSQL Server

  3. Keyboard shortcut: Ctrl + F5

 
CerebroSQL - mssql create page
 
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, changing the current database is also performed through the "Current database" drop-down list.

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

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

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 MSSQL Server

[CerebroSQL] MSSQL database list.jpg

-> Request to retrieve data <-

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

-----

SCHEMA LIST

-> Request to retrieve data <-

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

-----

 
TABLE

List of tables in the database grouped by schema

[CerebroSQL] Table list in database MSSQL.jpj

-> Request to retrieve data <-

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 tablle (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 the table and its indexes

  • Script - select - form and add a query text to the editor to select data from a table.

  • Script - insert - form and add to the editor the text of the insert command to insert data from the table.

  • Export data to CSV - run the wizard for exporting data from a table

  • Import data from CSV - run the wizard to import data from CSV file into the selected table

Node "COLUMNS"

List of table columns with data type

-> Request to retrieve data <-

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 node

List of indexes built on table columns

 
 
[CerebroSQL] Table - index info.jpg

Index list with detailed information

-> Request to retrieve data <-

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 by which the index is built

-> Request to retrieve data <-

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

-----

Node "CONSTRAINTS"

List of table constraints

 
[CerebroSQL] Table constraint list.jpg

-> Request to retrieve data <-

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

List of triggers on a table

[CerebroSQL] Table - trigger list.jpg

-> Request to retrieve data <-

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

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

[CerebroSQL] Table size.jpg

-> Request to retrieve data <-

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

List of views in the selected database grouped by schema

[CerebroSQL] View list in database MSSQL.jpg

-> Request to retrieve data <-

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

-----

Menu:

  • Show data - show view data. First 100 lines are fetched in dirty read mode (with (nolock))

  • Get source - generate and output a command to create a view to the editor

 
SYNONYM

List of synonyms in the selected database grouped by schema

[CerebroSQL] Synonym list in database MSSQL.jpg

-> Request to retrieve data <-

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

List of procedures in the selected database grouped by schemes

[CerebroSQL] Procedure list in database MSSQL.jpg

-> Request to retrieve data <-

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 ('sys')
order by 1

-----

Menu

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

-> Request to retrieve data <-

use $$DBNAME

go

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

-----

 
FUNCTION

List of functions in the selected database grouped by schemes

[CerebroSQL] Function list in database MSSQL.jpg

-> Request to retrieve data <-

select o.name "name", o.object_id
  from $$DBNAME.sys.all_objects o,
       $$DBNAME.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 the text of the procedure creation command to the editor

-> Request to retrieve data <-

use $$DBNAME

go

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

-----

 
TRIGGER

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

[CerebroSQL] Trigger list in database MSSQL.jpg

-> Request to retrieve data <-

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

-----

Menu

  • Disable trigger - create and add a command to the editor to disable the trigger

  • Enable trigger - create and add a command to the editor to enable the trigger

  • Get source - generate and add the command text to create a trigger to the editor

 
TYPE

List of types created in the selected database grouped by schema

[CerebroSQL] Type list in database MSSQL.jpg

-> Request to retrieve data <-

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

List of sequences created in the selected database grouped by schema

[CerebroSQL] Sequence list in database MSSQL.jpg

-> Request to retrieve data <-

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

List of roles and users created on the MSSQL server

[CerebroSQL] Security - list user.jpg

-> Request to retrieve data <-

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

Single center for control, monitoring and optimization

SESSION LIST page

List of sessions on the MSSQL server

[CerebroSQL] MSSQL Monitor - session list.jpg

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

-> Request to retrieve data <-

exec sp_who2

-----

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

-> Request to retrieve data <-

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 the "PLAN" tab, the query execution plan is displayed

Missing indexes page

List of indexes required to optimize the work of queries (increase the performance of the database).

The list of recommended indices is updated by pressing the F5 button

 
[CerebroSQL] MSSQL Monitor - missing index.jpg

-> Request to retrieve data <-

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 page

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

 
[CerebroSQL] MSSQL Monitor - wait stats.jpg
 

-> Request to retrieve data <-

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 page

MS SQL Server Parameter Editor

[CerebroSQL] MSSQL Monitor - edit configure.jpg

-> Request to retrieve data <-

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

-----

Change parameter value

  • Select a database from the list "Database name"

  • In the "Parameter list" select the desired configuration parameter

  • Enter a new value in the "New value" field

  • Press the "Apply" button

Server log page

Viewing the MSSQL Server operation logs

 
[CerebroSQL] MSSQL Monitor - server log view.jpg
 
Control buttons
[CerebroSQL] PostgreSQL list control button
  • Execute current SQL - Execute the currently selected query or all queries in the sheet

Execute - similar to clicking a button

To run the script from a file - execute the script from disk. The script is not loaded into the editor.

Check the script file for validate - check the script on disk for correctness without executing it

Show a list of command for execute - parse commands in the editor into execution blocks. The internal code and text of a single command is displayed

CerebroSQL for Oracle - execute menu
  • Commit - commit changes

  • Rollback - roll back changes made to data by the last requests

  • Stop execute - stop execution of the current request

  • Close open cursor - close the current dataset

  • Show plan - show the execution plan of the selected query

[CerebroSQL] PostgreSQL query plan execu

-> Request to retrieve data <-

EXPLAIN VERBOSE <query>

-----

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

  • Export data in file - exporting grid data (visible part of the data) to a file on disk in:

CerebroSQL for Oracle - export data

Export data in XLS - export data to a file in XLS format

Export data in TXT - export data to a file in TXT format

Export data in CSV - export data to a file in CSV format

Export data in HTML - export data to a file in HTML format

Export data in RTF - export data to a file in RTF format

  • Save current list in file - save the contents of the current sheet to a file on disk

  • Open file - open file manager

CerebroSQL for Oracle - open file
  • List saving code - open the manager for creating code blocks. Code blocks are code constructs saved in the program and can be accessed from the drop-down menu of the button

CerebroSQL - code block

Creating a block of code

  1. Press the "New" button

  2. From the drop-down list "Parent name" select the name of the root node. The root node is also the parent of the menu. To add a new entry to the list, click the "Add new parent" button and enter new values

  3. From the drop-down list "Page type" select the type of DBMS for which the code block should be displayed in the list

  4. In the "Code" field, enter the text of the code block, this text is added to the editor

  5. Click the "Save" button

CerebroSQL - code block saved
[CerebroSQL] Query block add list.jpg
 
 
  • Project manager - open the project manager

Workspace

 

This is a field on the sheet for entering queries / commands.

Splitting the code into its component parts

The engine of the query editor, before executing commands, parses the code, identifying the constituent parts.

To separate the code, special characters are used (; - semicolon, / - slash, go - keyword on a separate line).

The program supports two code syntaxes:

  • Classic Oracle syntax: a symbol is used to separate commands from each other; (semicolon) except for the create, begin, declate commands - these commands are separated from others by the / (slash) character on a separate line

  • MSSQL Server syntax: use the go keyword on a separate line to separate commands from each other

Any of two syntaxes are allowed, including both at the same time

[CerebroSQL] PostgreSQL query execute.jp
 

Logging of operations performed

The text of any command / query in the SQL language after execution is saved in the local storage (the Core database is the cer_history_query table). The following is recorded in the database: the text of the request / command actually executed by the program kernel, the execution status, the execution time, the number of changed lines, the DB messages (output)

CerebroSQL for Oracle - save query execute

In addition to saving data to the database, the progress of each command execution is recorded on the "Messages" tab of the sheet: the status of the commands execution, execution time, line numbers in the editor on which the executed command is located is displayed. These tabs are cleared before running command execution

CerebroSQL for Oracle - query executing messages
 

Icons on the left side of the window display the progress of execution (executed commands, current command, pending execution, query execution error, line with an error in the query)

Code hinter

The code hinter helps to speed up the writing of a query by suggesting the names of objects and columns.

For schemes - all objects of the scheme are displayed

[CerebroSQL] code compliter 1.jpg

For aliases of tables, views - a list of columns

[CerebroSQL] code compliter 2.jpg
 

As part of the object name - objects are displayed in the database whose name begins with the entered text. The tooltip is displayed by the key combination Crtl + Space

CerebroSQL for Oracle - code compliter l
 
Query aliases

In the program for queries, you can assign an alias and subsequently execute a query on it, see the details in the section " Query Alias ​​Manager "

Workspace menu
  • Copy - copy the selected text to the clipboard

  • Past - paste the contents of the clipboard into the editor window

  • Cut - cut the selected text

  • SelectAll - select all text on the stage

  • View table - viewing detailed information on the selected table

  • Get DDL - show the code for creating the selected table

  • Show command help - show help for the selected command

CerebroSQL for Oracle - Show selected command
[CerebroSQL] PostgreSQL help command.jpg
  • Show object data - show data of the selected table, view, synonym

  • Project manager -> Add object - create a page for the highlighted code in the current default project

CerebroSQL for Oracle - add code in project
  • Project manager -> Search object card - find the card of the selected object in the current project by default

  • Comment -> Add current line - add comment characters to the beginning of the current line

  • Comment -> Remove current line - remove comment characters at the beginning of the current line

  • Comment -> Add selected text - add comment characters to the beginning of each of the selected lines

  • Comment -> Remove selected text - remove comment characters at the beginning of each of the selected lines