Топ-100
 
CerebroSQL

TSQL editor for MSSQL SERVER DBMS

Didn't find the features you need? Write in the comments on  forum  and we will add the necessary 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