Menu ▼


<< < 1 2 3 4 > >>

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

Sometimes when you done changes in table columns and save, you will get the below error

Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created.

To resolve this issue simple do the below two setps.

  • Goto "Tools" -> Open "Options"
    SQL Tools Options
  • Select "Designers" -> "Table and Database Designers" and Uncheck the checkbox "Prevent saving changes that require table re-creation" and click "OK"
    Prevent saving changes that require table re-creation

Read More...
Submited By : mrkraju On 12 Feb 2016

In SQL server there is no inbuilt trim function, but we have inbuilt LTRIM() & RTRIM() functions. LTRIM() function will trim the left side (leading)spaces and RTRIM() function will remove right side(trailing) spaces in a string. So by using inbuilt LTRIM() & RTRIM() function we can write the user define TRIM() function to trim the leading and trailing spaces in a string.

Function

CREATE FUNCTION TRIM(@inputStr NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
BEGIN
    RETURN LTRIM(RTRIM(@inputStr))
END

Usage

SELECT dbo.TRIM(' Hello World                                        ')
Output
'Hello World'

Using in SELECT statement

SELECT dbo.TRIM(EmployeeName) FROM tblEmployee

Read More...
Submited By : mrkraju On 03 Dec 2015

SQL string REVERSE() function was introduced in SQL Server 2008. REVERSE() function will returns the reverse order of a string value. If you given integer value or datetime to reverse() function it will first convert to string value and then returns the reverse value of it.

Example

SELECT REVERSE('HELLO WORLD')
Output
DLROW OLLEH


Example

SELECT REVERSE('56789')
Output
98765


Example

SELECT REVERSE(GETDATE())
Output
MP74:01 5102 2 ceD


Example

Read More...
Submited By : mrkraju On 02 Dec 2015

By using regularexpesssion and PatIndex we can remove the non alphabetic characters from a string in sql server. Below sql function will take the input string and remove the special characters and non alphabets and returns the output string.

SQL function to remove non alphabets

CREATE Function [dbo].[RemoveNonAlphabets](@input VarChar(4000))
Returns VarChar(4000)
AS
Begin

    Declare @Alphabets as varchar(50)
    Set @Alphabets = '%[^a-z]%'
    While PatIndex(@Alphabets, @input) > 0
        Set @input = Stuff(@input, PatIndex(@Alphabets, @input), 1, '')

    Return @input
End

Example
SELECT dbo.RemoveNonAlphabets ('Hello 123 Welcome!')
Output
Hello Welcome

Note
If you dont want to remove the spaces then add a space in the regularexpesssion after z and before ] as below
Set @Alphabets = '%[^a-z ]%'

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

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
<< < 1 2 3 4 > >>




Search dotnetlearners.com