Database Console Commands

WHAT IS A DATABASE?

A database is an organized collection of data, generally stored and accessed electronically from a computer system. Where databases are more complex they are often developed using formal design and modeling techniques

TYPES OF DATABASE

Four types of database management systems

A relational database management system is one of four common types of systems you can use to manage your business data. The other three include:

  • hierarchical database systems
  • network database systems
  • object-oriented database systems

Hierarchical database model resembles a tree structure, similar to a folder architecture in your computer system. The relationships between records are pre-defined in a one to one manner, between ‘parent and child’ nodes. They require the user to pass a hierarchy in order to access needed data. Due to limitations, such databases may be confined to specific uses.

Network database models also have a hierarchical structure. However, instead of using a single-parent tree hierarchy, this model supports many to many relationships, as child tables can have more than one parent.

Finally, in object-oriented databases,the information is represented as objects, with different types of relationships possible between two or more objects. Such databases use an object-oriented programming language for development.

NoSQL or non-relational databases

A popular alternative to relational databases, NoSQL databases take a variety of forms and allow you to store and manipulate large amounts of unstructured and semi-structured data. Examples include key-value stores, document stores and graph databases.

WHAT IS DATABASE CONSOLE?

Query or database consoles are SQL files in which you can compose and execute SQL statements. Unlike scratch files, consoles are attached to a data source. You can use a query console in the same way as you use a terminal. Just type and run your code.

When you create a data source, a query console is created automatically. If necessary, you can create additional consoles. Each console creates a new connection. If you do not want to create new connections, enable single connection mode..

Working with console files

Create a query console

To create a console, use one of the following actions in the Database tool window (View | Tool Windows | Database ).

  • Click a data source and select File | New | Query Console.
  • Right-click a data source and select New | Query Console.
  • Click a data source, press Alt+Insert, and select Query Console.
  • Click a data source, press Ctrl+Shift+F10, and select New Query Console.

New console means a new connection to a data source. You can work with just one connection for the data source and have all consoles use that same one connection. Read more about the single connection mode in Single Connection Mode.

Open a console

When a console is open, you can write SQL here, run it and get the result.

  • In the Project tool window (View | Tool Windows | Project ), navigate to Scratches and Consoles | Database Consoles | <data_source_name>, double-click a console file.
  • In the Database tool window, click the Jump to Query Console icon (the Jump to console icon ) and select a console that you want to open or create a new one.
  • Click a data source, press F4, and select the console that you need.Open a console

Rename a query console

  1. In the Project tool window (View | Tool Windows | Project ), navigate to Scratches and Consoles | Database Consoles.
  2. Expand a data source group that includes your console.
  3. Right-click a console file and select Refactor | Rename.

Group consoles under the data source directory

  1. In the Project tool window (View | Tool Windows | Project ), navigate to Scratches and Consoles | Database Consoles.
  2. Right-click a data source group where you want to create a new directory, select New | Directory.
  3. Specify a name of the directory.
  4. Select and drag console files to the directory.
  5. In the Move dialog, click OK.

Delete a query console

  1. In the Project tool window (View | Tool Windows | Project ), navigate to Scratches and Consoles | Database Consoles.
  2. Expand a data source group that includes your console.
  3. Right-click a console file and select Delete.

Copy a console file to a new location

  1. In the Project tool window (View | Tool Windows | Project ), navigate to Scratches and Consoles | Database Consoles.
  2. Expand a data source group that includes your console.
  3. Right-click a console file and select Refactor | Copy File.
  4. In the To directory field, specify a new location for the file.

Configuring console settings

Modify console settings

  • Open a console and click the Data Source Properties icon (the Open Database Settings icon ).

Configuring console dialects

Change the SQL dialect in the editor

  • Right-click the editing area of the input pane and select Change Dialect (<current_dialect>). In the Change SQL Dialect window, select a dialect.Note that the selected dialect is applied to all the data sources that were created with the current driver. For example, let’s say that you have several data sources that use the PostgreSQL driver. If you change the dialect to Greenplum in one console, all the consoles and data sources that use the PostgreSQL driver will switch to the Greenplum dialect.https://i2.wp.com/resources.jetbrains.com/help/img/idea/2021.1/db_change_the_sql_dialect_in_the_editor.png?w=525&ssl=1Gif

By default, the SQL console dialect is defined by a data source that is attached to the console.

Disable syntax error highlighting

  • Right-click the editing area of the input pane, select Change Dialect, and select Generic SQL. The Generic SQL dialect supports completion and highlighting for SQL keywords, table and column names. Syntax error highlighting is disabled, that is all statements in a query console are shown as syntactically correct).

The change of the default dialect will influence all consoles and data sources that use the current data source driver.

DATABASE CONSOLE COMMANDS

DBCC stands for Database Console Commands (DBCC) or Database Consistency Checker. DBCC commands are used for  troubleshooting purposes. Microsoft SQL Server has introduced many DBCC commands, and there were a  few in Version 2000, 2005 and so on. With each version, they are adding new DBCC commands and these DBCC commands are helpful when you use them. When you start using them more often, you will notice how these commands are useful. 

Let’s Start

  • Open SSMS (SQL Server Management Studio)

    SSMS

    Connect SQL Server and select TEST database, where you want to try those DBCC commands.
  • DBCC help (‘?’)

    help

    We will start from DBCC Help, as you can see in the bottom all DBCC Commands are displayed there.
  • DBCC TRACEON

    TRACEON

    In Sp_who2, we will see SPID and just type DBCC TRACEON (SPID),

    code

    To set Trace off, just type DBCC TRACEOFF (SPID).

    code

    If there is any DLL, that’s accumulating memory, like stored procedures and so on.
  • DBCC dllname (FREE)

    code
  • CHECKTABLE
    1. USE DATABASENAME  
    2. DBCC CHECKTABLE (‘TABLENAME’)  It checks the pages in that table and its integrity constraints.

      code
  • CHECKIDENT
    1. USE DATABASENAME  
    2. DBCC CHECKIDENT (‘TABLENAME’)  CHECKIDENT

      As you see in the screenshot above, it checks the integrity value and the column value of that table.
  • CHECKDB
    1. USE MASTER  
    2. DBCC CHECKDB (‘DATABASENAME’)  CHECKDB

      CHECKDB

      CHECKDB

      You will see a detailed description which is in that database.
  • CheckFilegroup

    CheckFilegroup

    It gives a detailed description of allocation, tables, rows, indexes, views and so on.
  • CHECKALLOC

    CHECKALLOC

    It checks for the allocation and disk space of that database.
  • DBCC Inputbufer

    DBCC Inputbufer

    DBCC inputbuffer will give you a detailed information, which query is running; not just in parameter but SPID.
  • CLEANTABLE
    1. DBCC CLEANTABLE(DATABASENAME,’TABLENAME’,0)  CLEANTABLE

      Cleantable clears the space in the table.
  • DropcleanBuffers

    DropcleanBuffers

    Removes all buffer from the pool.
  • DBCC FREEPROCCACHE

    Delete all the elements in procedure cache,

    DBCC FREEPROCCACHE
  • DBCC SHRINKDATABASE

    DBCC SHRINKDATABASE

    It shrinks the database, but you have to specify the size of a log; how much you want to shrink in MB.
  • DBCC SHRINKFILE

    DBCC SHRINKFILE

    You have to specify the log file of the database and it will shrink log file again in MB.
  • DBCC SQLPERF(LOGSPACE)

    DBCC SQLPERF(LOGSPACE)

    It will show the log size of each database in MB and log space used.
  • DBCC OPENTRAN

     DBCC OPENTRAN

    This is used to find out the transactions that are running in the database. This is mostly used to find the oldest transaction and kill it .
  • DBCC SHOW_STATISTICS

    DBCC SHOW_STATISTICS

    This command is used to tune a query or tracing is done but you have to the pass table name and an index name .
  • DBCC UPDATEUSAGE

    DBCC UPDATEUSAGE

    This command is used to update data and space in sysindexes table and clears the space.

Conclusion

This sums up DBCC Commands in SQL Server, its uses and where we can use these commands.

Hits: 1

Related Post

Leave a Reply

Your email address will not be published.