Menu ▼


<< < 1 2 3 > >>

Below query will return the total number of tables in sql server

SELECT count(name) as tablecount FROM sys.sysobjects WHERE xtype = 'U'


Below query will return the total number of views in sql server

SELECT count(name) as viewscount FROM sys.sysobjects WHERE xtype = 'V'


Below query will return the total number of stored procedures in sql server

SELECT count(name) as spcount FROM sys.sysobjects WHERE xtype = 'P'


Below query will return the total number of functions in sql server

SELECT count(name) as fncount FROM sys.sysobjects WHERE xtype = 'FN'



If you want to get the names of all tables, views, stored procedures or functions then use the below queries

SELECT name FROM sys.sysobjects WHERE xtype = 'U'

SELECT name FROM sys.sysobjects WHERE xtype = 'V'

SELECT name FROM sys.sysobjects WHERE xtype = 'P'

SELECT name FROM sys.sysobjects WHERE xtype = 'FN'

Read More...
Submited By : mrkraju On 07 Jan 2017

When you truncate the table data, table Identity will be reset automatically. But when you delete the records in the table Identity will not be reset.

Below query will reset the table Identity in SQL server.

DBCC CHECKIDENT ('[TableName]', RESEED, 0);
GO

Read More...
Submited By : mrkraju On 15 Jun 2016

Below are the differences between Truncate and Delete in MS-SQL.

Delete Truncate
Can apply WHERE condition to filter records that need to be deleted. Cannot apply WHERE condition.
Delete is DML command. Truncate is DDL command.
Works against table row (locks the table row). Works against table (locks the table).
By using transactions you can rollback the deleted records. You cannot roll back the deleted records.
Trigger will fire when Delete the rows. Truncate cannot activate Trigger (Trigger will no fire)
No impact on identity. Resets the Column identity.

Read More...
Submited By : mrkraju On 15 Jun 2016

This example shows you how to restore SQL database from a backup file using SQL Query.

Example
USE MASTER
GO


ALTER DATABASE SampleDB
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Go


RESTORE DATABASE SampleDB
FROM DISK = N'E:\DB\Bakup\SampleDBBackupfile.bak'
Go


ALTER DATABASE SampleDB
SET MULTI_USER with ROLLBACK IMMEDIATE
Go


In the above example
SampleDB is the database name.
N'E:\DB\Bakup\SampleDBBackupfile.bak' physical path of backup file.

Read More...
Submited By : mrkraju On 28 Apr 2016

There are multiple ways to check the given date is weekend or not.

Sql function DATENAME returns the given date name as Sunday, Monday, Tuesday, ... We can compare the return value with Saturday and Sunday and can determine the given date is weekend or not.

Sql function DATEPART returns the given date number 1 to 7. Where 1-Sunday, 2-Monday, ... 7-Saturday. If the return value is 1 or 7 then the given date is weekend otherwise given date is weekday.

Example

DECLARE @dt DATETIME

SET @dt = GETDATE()

SELECT @dt

SELECT DATENAME(DW, @dt) -- Returns Datename as Sunday, Monday, Tuesday ....

SELECT DATEPART(DW, @dt)  -- Returns Date in number 1-Sunday, 2-Monday, ... 7-Saturday

Read More...
Submited By : mrkraju On 19 Nov 2015

The below query will return the file name from file full path in SQL Server.

Example

DECLARE @filepath VARCHAR(1000)
SET @filepath = 'E:\Data\InputFiles\EmployeeFeed.xlsx'
SELECT  RIGHT(@filepath, CHARINDEX('\', REVERSE(@filepath)) - 1)

Output -> EmployeeFeed.xlsx

Read More...
Submited By : mrkraju On 16 Nov 2015

Below query will give you the last day of the previous month.

SELECT DATEPART(day, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m, 0, GETDATE()),0)))

Below query will give you the last day of the current month.

SELECT DATEPART(day, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m, 0, GETDATE())+1,0)))

Below query will give you the last day of the next month.

SELECT DATEPART(day, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m, 0, GETDATE())+2,0)))

Example

Read More...
Submited By : mrkraju On 16 Nov 2015

The below query will pad leading zeros to number (integer).

SELECT RIGHT('0000'+CAST(numberField AS VARCHAR(4)),4)


Example
The below example will add padding zeros upto 4 digits.
sql add zeros to number

Read More...
Submited By : mrkraju On 08 Nov 2015

Below query will fetch the list of stored procedure names that uses the given specific text.

SELECT Name 
FROM sys.procedures 
WHERE OBJECT_DEFINITION(object_id) LIKE '%Search Text%' 

Read More...
Submited By : mrkraju On 15 Oct 2015

When we restore the database in SQL server, sometimes we may face problem in login with the existing login credentials in restored database. to fix the issue just run the below query by replacing the 'uname' with your login username.

Below SQL query will fixes the login problem for user 'uname' .

EXEC sp_change_users_login 'Auto_Fix', 'uname'

Read More...
Submited By : mrkraju On 15 Oct 2015
<< < 1 2 3 > >>




Search dotnetlearners.com