Below query will return the total number of tables in sql server
Below query will return the total number of views in sql server
Below query will return the total number of stored procedures in sql server
Below query will return the total number of functions in sql server
If you want to get the names of all tables, views, stored procedures or functions then use the below queries
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.
Below are the differences between Truncate and Delete in MS-SQL.
This example shows you how to restore SQL database from a backup file using SQL Query.
In the above example SampleDB is the database name. N'E:\DB\Bakup\SampleDBBackupfile.bak' physical path of backup file.
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.
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.
Using in SELECT statement
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.
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
ExampleSELECT dbo.RemoveNonAlphabets ('Hello 123 Welcome!')Output Hello Welcome
NoteIf you dont want to remove the spaces then add a space in the regularexpesssion after z and before ] as belowSet @Alphabets = '%[^a-z ]%'
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.
The below query will return the file name from file full path in SQL Server.
Output -> EmployeeFeed.xlsx