Magic tables

In SQL server magic table is nothing more than an internal table which is created by the SQL server to recover recently inserted, deleted and updated data into SQL server database. 

Generally we cannot see these two table, we can only see it with the help Trigger's in SQL server.  

 Using with Triggers:
If you have implemented any trigger for any Tables then,
1.Whenever you Insert a record on that table, That record will be there on INSERTED Magic table.
2.Whenever you Update the record on that table, That existing record will be there on DELETED Magic table and modified New data with be there in INSERTED Magic table.
3.Whenever you Delete the record on that table, That record will be there on DELETED Magic table Only.
These magic table are used inside the Triggers for tracking the data transaction.

Get all column of a table

Get all column of a table 

SELECT *FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='Customers'
go

sp_Help 'Customers'
go

SELECT *FROM sys.all_columns where object_id=OBJECT_ID('Customers')

Search by Column Value in All Referencing Tables in SQL SERVER

Search by Column Value in All Referencing Tables in SQL SERVER

DECLARE @sql nvarchar(255)
DECLARE @tablename nvarchar(255)
DECLARE @var varchar(50)
DECLARE @columnname nvarchar(100)
--Supply the column name and value here
SET @columnname= 'FirstName'
SET @var='FirstName'
DECLARE TableCol CURSOR FOR
SELECT t.name           
      FROM MyPractice2014.sys.columns AS c 
            INNER JOIN 
             MyPractice2014.sys.tables AS t 
            ON t.[object_id] = c.[object_id] 
        WHERE     UPPER(c.name) = @columnname order by t.name
OPEN TableCol
FETCH TableCol INTO @tablename
-- start the main processing loop.
WHILE @@Fetch_Status = 0
BEGIN
-- In this query 'Table_Name' is dummy column that'll display the current tablename as column header
SELECT @sql='select ''TABLE_NAME'' as '''+@tablename+''', * from '+@tablename+' where '+@columnname+'='+@var
EXEC(@sql)
--print @tablename
FETCH TableCol INTO @tablename
END
CLOSE TableCol
DEALLOCATE TableCol
RETURN
GO

list all table without clustered index

list all table without clustered index

SELECT DISTINCT [TABLE]=OBJECT_NAME(OBJECT_ID) FROM sys.indexes WHERE index_id=0
AND OBJECTPROPERTY(object_id,'IsUserTable')=1 ORDER BY [TABLE]

Get The list of Tables Without Primary Keys

Get The list of Tables Without Primary Keys

SELECT Name FROM sysobjects 
WHERE ID NOT IN (SELECT b.Id FROM Sysconstraints b, sysobjects c WHERE c.type ='k' AND c.Id=b.constid) AND 
type ='U' ORDER BY name

Get table definitions through T-SQL

--query table definitions

use AdventureWorks

DECLARE @TableName VARCHAR(128)
DECLARE @TableSchema VARCHAR(128)
--Identify the table name
SET @TableName ='Department'
--identify the table owner ex. dbo
SET @TableSchema='Humanresource'

SELECT Column_Name, COLUMN_DEFAULT,IS_NULLABLE AS ALLOW_NULL,DATA_TYPE,ISNULL(CHARACTER_MAXIMUM_LENGTH,0)[Max Length],
ISNULL(NUMERIC_PRECISION,0)[Precision],
ISNULL(NUMERIC_SCALE,0)[Scale]
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME=@TableName
AND TABLE_SCHEMA=@TableSchema ORDER BY ORDINAL_POSITION

Text Data Manipulation in SQL Server

Text Data Manipulation in SQL Server

CHARINDEX

CHARINDEX(findTextData, textData,[StartingPosition])

- Returns the specific postion of the specified expression in a character string
- Starting Postion is optional

SELECT CHARINDEX('s','Rishi Sanuj')
SELECT CHARINDEX('s','RishiSanuj',5)

LEFT

- LEFT(character_expression, integer_expression)
- Returns the left part of the character string with the specified number of character

SELECT LEFT('Sushant Priyadarshi',6)
SELECT LEFT('Sushant Priyadarshi',12)

LEN

LEN(textData)
- return the value of length of the string

SELECT LEN('Rishi')
SELECT LEN('Sushant Priyadarshi')

LOWER

LOWER(character_expression)

- return a character expression after converting upper case character data to lower case

SELECT LOWER('RISHI SANUJ')

LTRIM

LTRIM(textData)

- Remove leading blanks in string

SELECT LTRIM(' Rishi Sanuj') 

PATINDEX

PATINDEX(findTextData,textData)

--returns the position of starting data of string

SELECT PATINDEX('%sa%', 'Rishi kumar Sanuj')

REPLACE

REPLACE(textData,findTextData,replaceWithTextdata)

--Replace the text found in occurance with new data

SELECT REPLACE('Rishi KUMAR Sanuj','ishi','aj')

REVERSE

REVERSE(character_expression)

--returns the reverse character expression

SELECT REVERSE('Rishi')

REPLICATE

-REPLICATE(character_expression,integer_expression)

- Repeate a character for a specific number of time

SELECT REPLICATE(' Rishi kumar',5)

RTRIM

RTRIM(textData)

-- remove trailing blanks

SELECT RTRIM('Rishi ')

SPACES

SPACES(number of spaces)

- repeats space values a number of times

SELECT 'Rishi'+SPACE(5)+'Sanuj'

STUFF

STUFF(textData,start, length,insertTextData)

-- DELETE a specified number of length of character and insert another set of character at a specified starting Point

SELECT STUFF('RISHI KUMAR SANUJ',1,5,'Sushant')

SUBSTRING 

SUBSTRING(textData, startPoint,length)

--return portion of the string

SELECT SUBSTRING('Rishi Kumar',3,5)

UPPER

UPPER(textData)

--convert all text to UPPER Case

SELECT UPPER('rishi sanuj')

backup all SQL Server databases

--script to backup all databases

DECLARE @Name VARCHAR(50)
DECLARE @Path VARCHAR(256)
DECLARE @Filename VARCHAR(256)
DECLARE @FileDate VARCHAR(20)

SET @Path='c:\Backup\'
SELECT @FileDate=CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_Cursor CUrSOR FOR
SELECT Name FROM Master.dbo.sysdatabases WHERE Name NOT IN ('master','model','msdb','tempdb') 

OPEN db_Cursor
Fetch NEXT FROM db_Cursor INTO @name

WHILE @@FETCH_STATUS=0
BEGIN 
SET @Filename=@Path+@Name+'_'+@FileDate+'.bak'
backup database @name TO DISK =@Filename
FETCH NEXT FROM db_Cursor INTO @Name
END
CLOSE db_Cursor
DEALLOCATE db_Cursor

All Database Space Used and Free

To get all database space used and free in MS SQL server you can use following script.
USE master 
SET NOCOUNT ON
DECLARE @Kb float
DECLARE @PageSize float
DECLARE @SQL varchar(max)

SELECT @Kb = 1024.0
SELECT @PageSize=v.low/@Kb FROM master..spt_values v WHERE v.number=1 AND v.type='E'

IF OBJECT_ID('tempdb.dbo.#FileSize') IS NOT NULL  DROP TABLE #FileSize CREATE TABLE #FileSize (  DatabaseName sysname,  [FileName] varchar(max),  FileSize int,  FileGroupName varchar(max),  LogicalName varchar(max))
IF OBJECT_ID('tempdb.dbo.#FileStats') IS NOT NULL  DROP TABLE #FileStats CREATE TABLE #FileStats (  FileID int,  FileGroup int,  TotalExtents int,  UsedExtents int,  LogicalName varchar(max),  FileName varchar(max))
IF OBJECT_ID('tempdb.dbo.#LogSpace') IS NOT NULL  DROP TABLE #LogSpace CREATE TABLE #LogSpace (  DatabaseName sysname,  LogSize float,  SpaceUsedPercent float,  Status bit)
INSERT #LogSpace EXEC ('DBCC sqlperf(logspace)')
DECLARE @DatabaseName sysname
DECLARE cur_Databases CURSOR FAST_FORWARD FOR  SELECT DatabaseName = [name] FROM dbo.sysdatabases WHERE [name] <> 'RVR_FSA' ORDER BY DatabaseName OPEN cur_Databases FETCH NEXT FROM cur_Databases INTO @DatabaseName WHILE @@FETCH_STATUS = 0
  BEGIN
 print @DatabaseName
 SET @SQL = '
USE [' + @DatabaseName + '];
DBCC showfilestats;
INSERT #FileSize (DatabaseName, [FileName], FileSize, FileGroupName, LogicalName) SELECT ''' +@DatabaseName + ''', filename, size, ISNULL(FILEGROUP_NAME(groupid),''LOG''), [name]  FROM dbo.sysfiles sf; '
PRINT @SQL
 INSERT #FileStats EXECUTE (@SQL)
 FETCH NEXT FROM cur_Databases INTO @DatabaseName
  END

CLOSE cur_Databases
DEALLOCATE cur_Databases
SELECT
 DatabaseName = fsi.DatabaseName,
 FileGroupName = fsi.FileGroupName,
 LogicalName = RTRIM(fsi.LogicalName),
 [FileName] = RTRIM(fsi.FileName),
 DriveLetter = LEFT(RTRIM(fsi.FileName),2),  FileSize = CAST(fsi.FileSize*@PageSize/@Kb as decimal(15,2)),  UsedSpace = CAST(ISNULL((fs.UsedExtents*@PageSize*8.0/@Kb), fsi.FileSize*@PageSize/@Kb * ls.SpaceUsedPercent/100.0) as decimal(15,2)),  FreeSpace = CAST(ISNULL(((fsi.FileSize - UsedExtents*8.0)*@PageSize/@Kb), (100.0-ls.SpaceUsedPercent)/100.0 * fsi.FileSize*@PageSize/@Kb) as decimal(15,2)),  [FreeSpace %] = CAST(ISNULL(((fsi.FileSize - UsedExtents*8.0) / fsi.FileSize * 100.0), 100-ls.SpaceUsedPercent) as decimal(15,2))  FROM #FileSize fsi  LEFT JOIN #FileStats fs  ON fs.FileName = fsi.FileName  LEFT JOIN #LogSpace ls  ON ls.DatabaseName = fsi.DatabaseName  ORDER BY 5, 8 DESC


and its output is :

source: http://www.databasejournal.com/scripts/all-database-space-used-and-free.html