How to find the Custom text in a stored procedures using SQL Server

SELECT OBJECT_NAME(object_id)

    FROM sys.sql_modules

    WHERE OBJECTPROPERTY(object_id, 'IsProcedure') = 1

    AND definition LIKE '%yourText%'

Getting System Defined Procedure Text in SQL Server

Returns one row per system object that contains an SQL language-defined module.
System objects of type FN, IF, P, PC, TF, V have an associated SQL module. 

Getting System Defined Procedure text:

select object_name(m.object_id), * from sys.system_sql_modules m
inner join sys.system_objects t on m.object_id=t.object_id
where type='P'


select object_name(m.object_id) as name, * from sys.system_sql_modules m
inner join sys.system_objects t on m.object_id=t.object_id
where type='P' and name='sp_renamedb'


SQL Text of "sp_renamedb":

create procedure sys.sp_renamedb --- 1996/08/20 13:52   @dbname sysname,    -- old (current) db name   @newname sysname    -- new name we want to call it  as   -- Use sp_rename instead.   declare @objid int    -- object id of the thing to rename   declare @bitdesc varchar(30) -- bit description for the db   declare @curdbid int   -- id of database to be changed   declare @execstring nvarchar (max)     --  If we're in a transaction, disallow this since it might make recovery impossible.   set implicit_transactions off   if @@trancount > 0   begin    raiserror(15002,-1,-1,'sys.sp_renamedb')    return (1)   end     --  Only the SA can do this.   if not (is_srvrolemember('dbcreator') = 1)   begin    raiserror(15247,-1,-1)    return (1)   end     --  Make sure the database exists.   if not exists (select * from master.dbo.sysdatabases where name = @dbname)   begin    raiserror(15010,-1,-1,@dbname)    return (1)   end     --  Make sure that the @newname db doesn't already exist.   if exists (select * from master.dbo.sysdatabases where name = @newname)   begin    raiserror(15032,-1,-1,@newname)    return (1)   end     -- Check to see that the @newname is valid.   declare @returncode int   EXEC @returncode = sys.sp_validname @newname   if @returncode <> 0   begin    raiserror(15224,-1,15,@newname)    return(1)   end     -- Don't allow the names of master, tempdb, and model to be changed.   if @dbname in ('master', 'model', 'tempdb')   begin    raiserror(15227,-1,-1,@dbname)    return (1)   end     select @execstring = 'ALTER DATABASE '    + quotename( @dbname , '[')    + ' MODIFY NAME = '    + quotename( @newname , '[')     EXEC (@execstring)     if @@error <>  0   begin    -- No need to raiserror as the CREATE DATABASE will do so    return(1)   end     return (0) -- sp_renamedb  

Getting System Defined View Text in SQL Server

Returns one row per system object that contains an SQL language-defined module.
System objects of type FN, IF, P, PC, TF, V have an associated SQL module. 

Getting System Defined View text:

select object_name(m.object_id), * from sys.system_sql_modules m
inner join sys.system_objects t on m.object_id=t.object_id
where type='V'


select object_name(m.object_id) as name, * from sys.system_sql_modules m
inner join sys.system_objects t on m.object_id=t.object_id
where type='V' and name='systypes'


SQL Text of "systypes":

CREATE VIEW sys.systypes AS   SELECT    name,    xtype = system_type_id,    status = convert(tinyint, 1 - is_nullable),    xusertype = convert(smallint, user_type_id),    length = max_length,    xprec = precision,    xscale = scale,    tdefault = default_object_id,    domain = rule_object_id,    uid = convert(smallint, schema_id),    reserved = convert(smallint, 0),    collationid = convert(int, collationproperty(collation_name, 'collationid')),    usertype = convert(smallint, typepropertyex(user_type_id, 'oldusertype')),    variable = sysconv(bit, -- nvarchar, varchar or varbinary     case when system_type_id in (165, 167, 231) then 1 else 0 end),    allownulls = is_nullable,    type = xtypetotds(system_type_id, 0),    printfmt = convert(varchar(255), null),    prec = convert(smallint, -- ntext, image or text     case when system_type_id not in (34, 35, 99) then typepropertyex(user_type_id, 'precision') end),    scale = convert(tinyint, typepropertyex(user_type_id, 'scale')),    collation = collation_name   FROM sys.types 

Formulation of sys.all_objects

sys.objects + sys.system_objects == sys.all_objects

You can verify by its count.

select  count(*) from sys.objects
select  count(*) from sys.all_objects
select  count(*) from sys.system_objects

Finding all The User Defined Functions (UDF) in a Database in SQL Server

This helps to find all The User Defined Functions (UDF) in a Database.

USE AdventureWorks;
GO
SELECT name AS function_name
,SCHEMA_NAME(schema_id) AS schema_name
,type_desc,type
FROM sys.all_objects
WHERE type_desc LIKE '%FUNCTION%';
GO
 
-- Except System Defined one
USE AdventureWorks;
GO
SELECT name AS function_name
,SCHEMA_NAME(schema_id) AS schema_name
,type_desc,type
FROM sys.objects
WHERE type_desc LIKE '%FUNCTION%';
GO

Configuring Ad Hoc Distributed Queries in SQL SEREVER

exec sp_configure 'show advanced options', 1
RECONFIGURE;
exec sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE;

Output:

Configuration option 'show advanced options' changed from 1 to 1. 
Run the RECONFIGURE statement to install.

Configuration option 'Ad Hoc Distributed Queries' changed from 1 to 1. 
Run the RECONFIGURE statement to install.

Reset Identity column to default in SQL Server

The following line resets the Identity value for the Customer table to 
0 so that the next record added starts at 1.

Syntax:

DBCC CHECKIDENT('Customer', RESEED, 0)

Adding Identity Column in SELECT INTO Statement in SQL SERVER

Is used only in a SELECT statement with an INTO table clause to insert an identity column into a new table.

Note: More than one identity column cannot be in table.

-- Create table
CREATE TABLE MyOrders3
(
    ProductName varchar(20)
);

-- Creating Copy of 'MyOrders3' with additional IDENTITY column
select IDENTITY(int, 1,1) AS Id,* INTO MyOrdersIdentity
from MyOrders3

insert into MyOrdersIdentity values ('Samsung')
select * from MyOrdersIdentity

Usage of IDENT_INCR and IDENT_SEED function of Identity column in sql server

Here we are see how to get the Increment & Seed Settings of Indentity in the table.

IDENTITY(SEED,INCREMENT)== IDENTITY(10,5)

Query Snippet:

 -- Create table with identity column
CREATE TABLE MyOrders2
(
    OrderID int IDENTITY(10,5),
    ProductName varchar(20)
);
select IDENT_INCR('MyOrders2') -- 5
select IDENT_SEED ('MyOrders2') -- 10
drop table MyOrders2

-- Create table with identity column
CREATE TABLE MyOrders2
(
    OrderID int IDENTITY(100,50),
    ProductName varchar(20)
);
select IDENT_INCR('MyOrders2') -- 50
select IDENT_SEED ('MyOrders2') – 100
Try After Reseting the Indentity by the below command:

dbcc CHECKIDENT('MyOrders2',RESEED,10)

It still shows the original setup in the table not the reset value by “CHECKIDENT” command.

select IDENT_INCR('MyOrders2') -- 50
select IDENT_SEED ('MyOrders2') – 100

Copying the Table and Data using SELECT INTO STATEMENT in SQL SERVER

It creates a new table in the destination, so if any table having same name in destination, then drop and continue, otherwise it throws error.

 -- Created table with Primary Key

CREATE TABLE MyOrdersPrimary
(
    OrderId int PRIMARY KEY NOT NULL,
    ProductName varchar(20)
);

-- Inserted some records

insert into MyOrdersPrimary values (1,'Samsung')
insert into MyOrdersPrimary values (2,'Nokia')
select * from MyOrdersPrimary

-- COPY THE (TABLE + DATA).

Select * into MyOrdersPrimaryCopy from MyOrdersPrimary

Note: the above wont copy the primary key to the copy table if you use 

SELECT INTO STATEMENT, only it copies table and data.

To Overcome the ABOVE problem:

If you want the complete Table structure,

CREATE Table by using source table script  and then use  "INSERT INTO" to copy data.

Finding total size of the database in sql server

sp_spaceused Returns information about the total size of the current database.

Syntax:

Exec sp_spaceused

Getting DATA & LOG File information of ALL Database in SQL SERVER

This helps to get all the data and log file information like physical path.size etc of all databases available in the server.

EXECUTE master.sys.sp_MSforeachdb 'USE [?]; EXEC sp_helpfile'

Finding total Size/Space of the Table in sql server

sp_spaceused 'MyTable' Returns information about the size of MyTable

Syntax:

Exec sp_spaceused 'TableName'

Query:

Exec sp_spaceused 'MyOrdersPrimary'

Finding Size of ALL TABLES in the Current Database

List the space used in all tables within the currently connected database.

It takes all the tables from the currently connected database and displays the size of the table in the result window.

exec sp_msforeachtable "sp_spaceused '?'"

Finding Data Size of column in Bytes in SQL SERVER

Returns the number of bytes used to represent any expression.

Here the “datalength “ funcion helps to find the size of the data in the column. For example data in the column is “ARUN” then it takes 4 bytes, because one charater takes one byte, likewise you can calculate for all datatypes.

-- Created table with Primary Key
CREATE TABLE MyOrdersPrimary
(
    OrderId int PRIMARY KEY NOT NULL,
    ProductName varchar(20)
);

-- Inserted some records
insert into MyOrdersPrimary values (1,'Samsung')
insert into MyOrdersPrimary values (2,'Nokia')
select datalength(ProductName) as Bytes, * from MyOrdersPrimary

Finding the Size of INDEX on Specific TABLE in SQL SERVER

use Northwind

SELECT object_name(object_id) as TableName, *

FROM sys.indexes

--Syntax

exec sp_spaceused 'TableName'

-- Check the size of index

exec sp_spaceused 'Employees'

Finding the Size of INDEX on ALL TABLE in the current Database in SQL SERVER

Gives the list of index in the specified database.

use Northwind

SELECT object_name(object_id) as TableName, *

FROM sys.indexes

-- Get the indexes from all the table in the current database

exec sp_msforeachtable "sp_spaceused '?'"

Configuring Database Mailing in SQL SERVER

Here we are going to see how to configure the database mailing, just execute the below code to configure.

sp_configure N'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure N'Database Mail XPs', 1;
GO
RECONFIGURE;
GO

Check Using below one:

select * from sys.configurations where name ='Database Mail XPs'

Getting Column information of specified table in SQL Server

To get details about the columns of a table, you can execute the sp_columns stored procedure.

Its syntax is:

sp_columns [ @table_name = ] object  [ , [ @table_owner = ] owner ]
     [ , [ @table_qualifier = ] qualifier ]
     [ , [ @column_name = ] column ]
     [ , [ @ODBCVer = ] ODBCVer ]

This procedure can take many arguments but one is required. The required argument is the name of the 

Example:

USE NORTHWND
GO
sp_columns N'Employees'

Deleting or Dropping an Alias Data Type in SQL SERVER

Imagine you had created a custom data type for your database:

USE Northwind;
GO
CREATE TYPE NaturalNumber FROM int;
GO

If you don't need such a data type any more, to assist you with removing it, Transact-SQL provides thesp_droptype. Its syntax is:

sp_droptype [ @typename= ] 'type'

This procedure takes one argument as the name of the custom data type you want to delete. Here is an example of executing it:

sp_droptype NaturalNumber;
GO

Dynamically Refreshing a View in SQL SERVER

The sp_refreshview stored procedure allows you to update the metadata of a view.

The syntax of this procedure is:

        sp_refreshview [ @viewname= ] 'viewname'

Here is an example that executes this procedure:

USE NORTHWND;
GO
sp_refreshview N'Invoices';
GO

Getting Size of ALL Databases in the Current server

Use Master
Go
EXECUTE sys.sp_MSforeachdb 'USE [?]; EXEC sp_spaceused'

Getting All Columns from entire Database in SQL Server

Here we can get all columns from all tables from entire database.

? – Indicates the name of database.

sp_msforeachdb 'select "?" AS dbName, * from [?].sys.columns