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  

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 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 '?'"

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