Posts
SqlAndMe shared a link.
sp_helptext is widely used for checking object definition in SQL Server. sp_helptext can be used to check definition of various database objects like Views, Stored Procedures and User Defined Functions. There are two other options which can be used to retrieve object definition: OBJECT_DEFINITION( o
SqlAndMe
SqlAndMe shared a link.
By default, SQL Server system objects are listed in Object Explorer in Management Studio. These system objects include system database, system tables/views/procedures and so on. SQL Server Management Studio provides an option to hide these objects from Object Explorer to prevent *accidental* use. To
SqlAndMe
Posts
SqlAndMe shared a link.
Modify date and create date for a table can be retrieved from sys.tables catalog view. When any structural changes are made the modify date is updated. It can be queried as follows: sys.tables only shows modify date for structural changes. If we need to check when was the tables last updated or
SqlAndMe
SqlAndMe shared a link.
You can enable line numbers to be displayed in SSMS Query Editor. This is extremely useful when working on a large module. To enable line numbers in Query Editor windows, follow below steps: Step1: Go to Tools > Options Step2: In the Options dialog box navigate to Text Editor > Transact-SQL > Genera
SqlAndMe
SqlAndMe shared a link.
@@MAX_CONNECTIONS in SQL Server returns maximum number of simultaneous user connections allowed. Maximum user connections allowed by SQL Server by default is 32,767; this number also depends on application and server hardware limits. This cam also be configured at server-level to avoid too many conn
SqlAndMe
SqlAndMe shared a link.
A Recovery Model is property of a database which control how transaction log is maintained. SQL Server supports SIMPLE, FULL and BULK-LOGGED recovery models. There are multiple ways to check recovery model of a database in SQL Server. 1. Using SQL Server Management Studio: Right click on Database in
SqlAndMe
Elapsed time can be calculated from DATETIME field by extracting number of hours/minutes and seconds. You can use below query to calculate elapsed time between two dates: Result Set: DD:HH:MM:SS StringFormat 4:13:2:55 4 Days 13 Hours 2 Minutes 55 Seconds (1 row(s) affected) Hope This Helps
SqlAndMe
Before an existing database can be restored, there should be connections using the database in question. If the database is currently in use the RESTORE command fails with below error: Msg 3101, Level 16, State 1, Line 2 Exclusive access could not be obtained because the database is in use. Msg 3013
SqlAndMe
ORDER BY clause can be used to sort the results returned by SELECT statement in SQL Server. It orders the result set by specified column list. When used with character data type columns it sorts data in dictionary-order. Sometimes, we need result set to be sorted in a custom order, for example, a sp
SqlAndMe
SqlAndMe shared a link.
Earlier on my blog I posted on How to get SQL Server Service Account using T-SQL. That works on a single instance. If you multiple instances of SQL Server installed on a server you can use below script to get SQL Server and SQL Server Agent service account information for all local instances: The ab
SqlAndMe
SqlAndMe shared a link.
SQL Server service account information is stored in Windows Registry database. You can get this information from Services Console or SQL Server Configuration Manager. To get account information from Services Console: 1. Go to Start > Run > Services.msc 2. Right Click on SQL Server Service, i.e. “SQL
SqlAndMe
SqlAndMe shared a link.
There are several options available to import data from external sources to SQL Server. Such as Import & Export Wizard, BULK INSERT command, SSIS and OPENROWSET. Apart from this options you can also use xp_cmdshell to import text file to SQL Server. We need to utilize dos command TYPE for this purpo
SqlAndMe
SqlAndMe shared a link.
This is a follow-up post on SQL Server – Check SQL Agent Job History using T-SQL. SQL Server Agent stores SQL jobs history in sysjobhistory. It has two different columns for date and time, Run_Date and Run_Time. Since this is not available as DATETIME we cannot filter based on certain criteria such
SqlAndMe
SqlAndMe shared a link.
To SELECT only top N rows from a table we can use TOP clause in SELECT statement. Using TOP clause we can also specify percentage option. For example, both of these statements are valid: USESqlAndMe GO SELECTTOP 5 EmployeeId,FirstName,LastName FROM dbo.Table_Employees GO SELECTTOP 5 PERCENTEmp
SqlAndMe
SqlAndMe shared a link.
July 8, 2013
By default SQL Server ERRORLOG is stored in "C:\Program Files\Microsoft SQL Server\InstanceFolder\MSSQL\Log" folder. The ERRORLOG location is configured as a startup parameter for SQL Server Service. To change the location of ERRORLOG you need to modify the startup parameter -e. For example, if you
SqlAndMe
SqlAndMe shared a link.
July 1, 2013
If you have lost the password for sa account or does not have any administrative account and you are locked out of SQL Server you can still login to SQL Server as an Administrator using Local administrator account. You can use any account which is part of local Administrators group. To be able to
SqlAndMe
SqlAndMe shared a link.
When an account is disable it cannot be used to login to SQL Server. You will receive below error when logging-in to SQL Server using a disabled Login account: You can enable a disabled SQL Server Login using SQL Server Management Studio or via T-SQL code. To enable a Login using SQL Server Manage
SqlAndMe
SqlAndMe shared a link.
Often we may need to update a column in a table based of another column in another table. In SQL Server you can do this using UPDATE statement by joining tables together. To understand this better let’s take a look at below contrived example. USE [SqlAndMe] GO SELECT CustomerID, Name, OrderAmount
SqlAndMe