Mustafa EL-Masry SQl server Administration

How to list disk drives with total and free space?


Execute the following Microsoft SQL
Server T-SQL script to create a table-valued function and a stored procedure to
list all drives with total disk space and free disk space. Note that the
table-valued function “join”-ed with a CROSS APPLY in the sproc to obtain the
final results.

*

— Turn on OLE automation if not on

exec sp_configure ‘show advanced options’, 1

go

RECONFIGURE

GO

exec sp_configure ‘Ole Automation Procedures’, 1

GO

RECONFIGURE

GO

*/

CREATE FUNCTION fnTotalDriveSpace

(@DriveLetter CHAR(1))

RETURNS @Total TABLE (MaxSpaceGB money)

BEGIN

DECLARE  @return INT, @fso INT, @GetDrive VARCHAR(16)

DECLARE @drv INT, @DriveSizeinBytes VARCHAR(32)

SET @GetDrive = ‘GetDrive(“‘ + @DriveLetter + ‘”)’

EXEC @return = sp_OACreate ‘Scripting.FileSystemObject’, @fso OUTPUT

SET @DriveSizeinBytes = NULL

IF @return = 0

EXEC @return = sp_OAMethod @fso, @GetDrive, @drv OUTPUT

IF @return = 0

EXEC @return = sp_OAGetProperty @drv,’TotalSize’, @DriveSizeinBytes OUTPUT

EXEC sp_OADestroy @drv

EXEC sp_OADestroy @fso

INSERT @Total values (

(((convert(bigint,@DriveSizeinBytes)/  1024)/ 1024)/1024) )

RETURN

END

GO

— select * from dbo.fnTotalDriveSpace(‘C’)

CREATE PROC sprocDriveSpaceInfo

AS

BEGIN

DECLARE @Drives TABLE ( DriveLetter char(1), FreeGB money)

INSERT @Drives (DriveLetter, FreeGB)

EXEC xp_fixeddrives

UPDATE @Drives SET FreeGB = Floor(FreeGB/1024)

SELECT

DriveLetter,

FreeGB=convert(int,FreeGB),

MaxSpaceGB=convert(int,MaxSpaceGB)

FROM @Drives d

CROSS APPLY dbo.fnTotalDriveSpace (d.DriveLetter)

ORDER BY DriveLetter

END

GO

EXEC sprocDriveSpaceInfo

GO

Mustafa EL-Masry Transact-SQL

Use insert into select instead of cursor


In this article I will explain by code how can you make cursor to select data from table and insert it into another table but this to learning but if you want to insert data from Table to another Table I suggest to use Insert into select instead of Cursor

— Create Table and Populate with Sample Data
CREATE TABLE ServerTable (ServerID INT, ServerName VARCHAR(100))
INSERT INTO ServerTable (ServerID, ServerName)
SELECT 1, ‘First Server’
UNION ALL
SELECT 2, ‘Second Server’
UNION ALL
SELECT 3, ‘Third Server’
— Creating New Table
CREATE TABLE NewServerTable (ServerID INT, ServerName VARCHAR(100))
— Insert Logic
DECLARE @Flag INT
SELECT @Flag = COUNT(*) FROM ServerTable
WHILE(@Flag > 0)
BEGIN
INSERT INTO NewServerTable (ServerID, ServerName)
SELECT ServerID, ServerName
FROM ServerTable
WHERE ServerID = @Flag
SET @Flag = @Flag – 1
END
SELECT ServerID, ServerName
FROM NewServerTable
— Clean up
DROP TABLE ServerTable
DROP TABLE NewServerTable

Remarks

To see what happens when you run this script enter F11 to open Debug Query and enter F10 to see effect step by step

Mustafa EL-Masry Script

Multiple Inserts in one statement – Row Constructor in SQL Server 2008


We all know how to use Data Manipulation
Language to Insert, Delete and Update data on a SQL Server table. Sometimes we
need to insert too much data using insert statements. There are traditional and
cumbersome methods to insert huge amounts of data. Now, SQL Server 2008 provides
a new method to insert data to SQL Server tables, which simplifies the data
insertion. This article is going to illustrate different methods to insert data
to a table, including the new Row Value Constructor.

Traditionally we have three methods to insert data

Method 1

USE [master]
GO
/****** Object:  Database [MyTestDB]
Script Date: 01/09/2011 ******/
IF  EXISTS (SELECT name FROM sys.databases
WHERE name = N’MyTestDB’)
DROP DATABASE [MyTestDB]
GO
Create database MyTestDB
Go
Use [MyTestDB]
Go
IF  EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[MyTest1]’)
AND type in (N’U’))
DROP TABLE [dbo].[MyTest1]
GO
USE [MyTestDB]
GO
/****** Object:  Table [dbo].[MyTest1]
Script Date: 01/09/2011 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MyTest1](
[Id] [int] NULL,
[Fname] [varchar](100) NULL,
[Lname] [varchar](100) NULL,
[salary] [money] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

Now let’s add 5 rows of data using a traditional ANSI insert SQL statement . Here we are going to use the INSERT SQL Statement with a VALUE clause to insert data.

insert into MyTest1 (id ,fname ,lname , salary) values (1 , ‘Mostafa’, ‘Elmasry’, 150000.00)
insert into MyTest1 (id ,fname ,lname , salary) values (2 , ‘Mohamed’, ‘Osman’, 250000.00)
insert into MyTest1 (id ,fname ,lname , salary) values (3 , ‘Mohamed’, ‘Omar’, 120000.00)
insert into MyTest1 (id ,fname ,lname , salary) values (4 , ‘Mahmoud’, ‘Darwish’, 151000.00)
insert into MyTest1 (id ,fname ,lname , salary) values (5 , ‘Khaled’, ‘Jamal’ , 150300.00)

Method 2

CREATE TABLE [dbo].[MyTest2](
[Id] [int] NULL,
[Fname] [varchar](100) NULL,
[Lname] [varchar](100) NULL,
[salary] [money] NULL
) ON [PRIMARY]

Now let’s add 5 rows of data using a traditional insert SQL statement . Here we are going to use the INSERT SQL Statement with a SELECT clause to insert data.

insert into MyTest1 select 1 , ‘Mostafa’, ‘Elmasry’, 150000.00
insert into MyTest1 select 2 , ‘Mohamed’, ‘Osman’, 250000.00
insert into MyTest1 select 3 , ‘Mohamed’, ‘Omar’, 120000.00
insert into MyTest1 select 4 , ‘Mahmoud’, ‘Darwish’, 151000.00
insert into MyTest1 select 5 , ‘Khaled’, ‘Jamal’ , 150300.00

Method 3

CREATE TABLE [dbo].[MyTest3](
[Id] [int] NULL,
[Fname] [varchar](100) NULL,
[Lname] [varchar](100) NULL,
[salary] [money] NULL
) GO

Now let’s add 5 rows of data using a traditional insert SQL statement . Here we are going to use the INSERT SQL Statement with a SELECT and UNION clause to insert data.

insert into MyTest3
select 1 , ‘mostafa’ , ‘Elmasry’ , 150000.00
union select 2 , ‘Mohamed’ , ‘Osman’ , 250000.00
union select 3 , ‘mohamed’ , ‘omar’ , 120000.00
union select 4 , ‘mohamed’ , ‘Ali’ , 151000.00
union select 5 , ‘mohamed’ , ‘na7la’ , 150300.00
*****—-Method 4—–******

CREATE TABLE [dbo].[MyTest4](
[Id] [int] NULL,
[Fname] [varchar](100) NULL,
[Lname] [varchar](100) NULL,
[salary] [money] NULL
)

insert into MyTest4 (id ,fname ,lname , salary) values
(1 , ‘A’ , ‘B’ , 12.00),
(2 , ‘C’ , ‘D’ , 13.00),
(3 , ‘E’ , ‘F’ , 14.00),
(4 , ‘G’ , ‘H’ , 15.00),
(5 , ‘I’ , ‘J’ , 16.00)

Mustafa EL-Masry Script

FizzBuzz in T-SQL


Write a program that prints the numbers from 1 to 100. But for multiples of three print “Fizz” instead of the number and for the multiples of five print “Buzz”. For numbers which are multiples of both three and five print “FizzBuzz”

————————————————————————————————-

select
number,
case when number%(3*5)=0 then ‘Mostafa Mohamed Elmasry’
when number%5=0 then ‘Elmasry’
when number%3=0 then ‘Mostafa’
else
cast(number as varchar(10))
end as MyName
from
master..spt_values
where
type=’p’ and number between 1 and 100
———————————————————————
WITH Numbers(Number) AS (
SELECT 1
UNION ALL
SELECT Number + 1
FROM Numbers
WHERE Number < 100
)
SELECT
CASE
WHEN Number % 3 = 0 AND Number % 5 = 0 THEN ‘Mostafa Mohamed Elmasry’
WHEN Number % 3 = 0 THEN ‘Mostafa’
WHEN Number % 5 = 0 THEN ‘Elmasry’
ELSE CONVERT(VARCHAR(3), Number)
END as myname
FROM Numbers
ORDER BY Number
———————————————————————-
DECLARE @counter INT
DECLARE @output VARCHAR(8)
SET @counter = 1
WHILE @counter < 101
BEGIN
SET @output = ”
IF @counter % 3 = 0
SET @output = ‘Fizz’
IF @counter % 5 = 0
SET @output = @output + ‘Buzz’
IF @COUNTER %(3*5) = 0
SET @OUTPUT = ‘Mostafa Elmasry’
IF @output = ”
SET @output = @counter
PRINT @output
SET @counter = @counter + 1
END
———————————————————–
DECLARE @num INT = 100

SET STATISTICS TIME ON;
SET STATISTICS IO ON;

;WITH
L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
L1   AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
L2   AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
L3   AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
L4   AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
L5   AS(SELECT 1 AS c FROM L4 AS A, L4 AS B),
Numbers AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Number FROM L5)

SELECT TOP (@num)
Number,
CASE
WHEN Number % 15 = 0 THEN ‘FizzBuzz’
WHEN Number % 3 = 0 THEN ‘Fizz’
WHEN Number % 5 = 0 THEN ‘Buzz’
ELSE CONVERT(VARCHAR(7), Number)
END AS FizzBuzz
INTO #FizzBuzz
FROM Numbers

SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;

SELECT FizzBuzz FROM #FizzBuzz ORDER BY Number

DROP TABLE #FizzBuzz

Mustafa EL-Masry SQl server Administration

How to get information about all databases


‎/*
**********For All Database Administrator (DBA)******************
*****How to get information about all databases without a loop********
1-ndexes in all databases with their usage (1 Script)
2-Indexes in all databases with their physical stats (1 Script)
3-Count of all objects in all databases (1 Script)
4-Quick Record Count in All Tables in All Databases (2 Script)
5-All Schema Names in All Databases (1 Script)
6-List of All Tables in All Databases (2 Script)
7-LIST OF ALL procedures IN ALL DATABASES (1 Script)
8-LIST OF ALL VIEWS IN ALL DATABASES (1 Script)
9- Size Tales in Database Return
 (Count Of Rows,Reserved Size,Data Size,Index Size,UnUsed Size) (2 Script)
10-Database File Size (MDF.LDF.NDF)in ALL Databases (1 Script)
11-Database SIZE (2 Script)
*/
——————————————————————————————
—How do I know which version of SQL Server I’m running?
SELECT ‘SQL Server ‘
+ CAST(SERVERPROPERTY(‘productversion’) AS VARCHAR) + ‘ – ‘
+ CAST(SERVERPROPERTY(‘productlevel’) AS VARCHAR) + ‘ (‘
+ CAST(SERVERPROPERTY(‘edition’) AS VARCHAR) + ‘)’
—————————————————————————————————–
–Some Info to all databases
SELECT database_id,
CONVERT(VARCHAR(25), DB.name) AS dbName,
CONVERT(VARCHAR(10), DATABASEPROPERTYEX(name, ‘status’)) AS [Status],
state_desc,
(SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = ‘rows’) AS DataFiles,
(SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = ‘rows’) AS [Data MB],
(SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = ‘log’) AS LogFiles,
(SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = ‘log’) AS [Log MB],
user_access_desc AS [User access],
recovery_model_desc AS [Recovery model],
CASE compatibility_level
WHEN 60 THEN ’60 (SQL Server 6.0)’
WHEN 65 THEN ’65 (SQL Server 6.5)’
WHEN 70 THEN ’70 (SQL Server 7.0)’
WHEN 80 THEN ’80 (SQL Server 2000)’
WHEN 90 THEN ’90 (SQL Server 2005)’
WHEN 100 THEN ‘100 (SQL Server 2008)’
END AS [compatibility level],
CONVERT(VARCHAR(20), create_date, 103) + ‘ ‘ + CONVERT(VARCHAR(20), create_date, 108) AS [Creation date],
— last backup
ISNULL((SELECT TOP 1
CASE TYPE WHEN ‘D’ THEN ‘Full’ WHEN ‘I’ THEN ‘Differential’ WHEN ‘L’ THEN ‘Transaction log’ END + ‘ – ‘ +
LTRIM(ISNULL(STR(ABS(DATEDIFF(DAY, GETDATE(),Backup_finish_date))) + ‘ days ago’, ‘NEVER’)) + ‘ – ‘ +
CONVERT(VARCHAR(20), backup_start_date, 103) + ‘ ‘ + CONVERT(VARCHAR(20), backup_start_date, 108) + ‘ – ‘ +
CONVERT(VARCHAR(20), backup_finish_date, 103) + ‘ ‘ + CONVERT(VARCHAR(20), backup_finish_date, 108) +
‘ (‘ + CAST(DATEDIFF(second, BK.backup_start_date,
BK.backup_finish_date) AS VARCHAR(4)) + ‘ ‘
+ ‘seconds)’
FROM msdb..backupset BK WHERE BK.database_name = DB.name ORDER BY backup_set_id DESC),’-‘) AS [Last backup],
CASE WHEN is_fulltext_enabled = 1 THEN ‘Fulltext enabled’ ELSE ” END AS [fulltext],
CASE WHEN is_auto_close_on = 1 THEN ‘autoclose’ ELSE ” END AS [autoclose],
page_verify_option_desc AS [page verify option],
CASE WHEN is_read_only = 1 THEN ‘read only’ ELSE ” END AS [read only],
CASE WHEN is_auto_shrink_on = 1 THEN ‘autoshrink’ ELSE ” END AS [autoshrink],
CASE WHEN is_auto_create_stats_on = 1 THEN ‘auto create statistics’ ELSE ” END AS [auto create statistics],
CASE WHEN is_auto_update_stats_on = 1 THEN ‘auto update statistics’ ELSE ” END AS [auto update statistics],
CASE WHEN is_in_standby = 1 THEN ‘standby’ ELSE ” END AS [standby],
CASE WHEN is_cleanly_shutdown = 1 THEN ‘cleanly shutdown’ ELSE ” END AS [cleanly shutdown]
FROM sys.databases DB
ORDER BY dbName, [Last backup] DESC, NAME
———————————————————————————————
—–1. To list down all databases in the server—–
SELECT NAME AS [DataBase Name] FROM SYS.DATABASES ORDER BY NAME
———————————————————————————————-
–2. To list down all databases in the server along with the state(online / offline / restoring / — recovering / recovering_pending / suspect / emergency)—–
SELECT NAME AS [Database Name],STATE_DESC AS [Database Status]  FROM SYS.DATABASES  ORDER BY NAME
———————————————————————————
—–3. To check the state of a particular database—–
SELECT NAME AS [Database Name],STATE_DESC AS [Database State]
FROM SYS.DATABASES WHERE NAME = ‘RGEN2011’
———————————————————————————————————-

–Indexes in all databases with their usage
DECLARE @SQL NVARCHAR(MAX)
IF OBJECT_ID(‘tempdb..#Result’,’U’) IS not NULL
DROP TABLE #Result
CREATE TABLE #Result (DBName sysname, TableName Sysname, IndexName sysname, USAGE BIGINT)

SELECT @SQL = COALESCE(@SQL,”) + CHAR(13) + CHAR(10) + ‘ use ‘ + QUOTENAME([Name]) + ‘;
insert into #Result select ‘ + QUOTENAME([Name],””) + ‘ as DbName,
object_name(i.object_id) as tablename, i.name as indexname,
s.user_seeks + s.user_scans + s.user_lookups + s.user_updates as usage
from sys.indexes i
inner join sys.dm_db_index_usage_stats s
on s.object_id = i.object_id
and s.index_id = i.index_id
and s.database_id = db_id()
where objectproperty(i.object_id, ”IsUserTable”) = 1
and i.index_id > 0
order by usage;’ FROM sys.databases
–print (@SQL)
EXECUTE (@SQL)
SELECT * FROM #Result ORDER BY [DbName],[USAGE]
DROP TABLE #Result
———————————————————————
–Indexes in all databases with their physical stats
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = ”
SELECT @SQL = @SQL +
‘Select ‘ + QUOTENAME(name,””) + ‘ as [DB Name],
object_Name(PS.Object_ID,’ + CONVERT(VARCHAR(10),database_id) + ‘) as [Object],
I.Name as [Index Name], PS.Partition_Number, PS.Index_Type_Desc,
PS.alloc_unit_type_desc, PS.index_depth, PS.index_level,
PS.avg_fragmentation_in_percent, PS.fragment_count, PS.avg_fragment_size_in_pages,
PS.page_count, PS.avg_page_space_used_in_percent, PS.record_count,
PS.ghost_record_count, PS.version_ghost_record_count,
PS.min_record_size_in_bytes, PS.max_record_size_in_bytes, PS.avg_record_size_in_bytes,
PS.forwarded_record_count, PS.compressed_page_count
from ‘ + QUOTENAME(name) + ‘.sys.dm_db_index_physical_stats(‘ +
CONVERT(VARCHAR(10),database_id) + ‘, NULL, NULL, NULL, NULL) PS
INNER JOIN ‘ + QUOTENAME(name) +
‘.sys.Indexes I on PS.Object_ID = I.Object_ID and PS.Index_ID = I.Index_ID ‘
+ CHAR(13)

FROM sys.databases WHERE state_desc = ‘ONLINE’

EXECUTE(@SQL)
———————————————————————————-
–Count of all objects in all databases
DECLARE @Qry NVARCHAR(MAX)
SELECT @Qry = COALESCE(@Qry + CHAR(13) + CHAR(10) + ‘ UNION ALL ‘,”) + ‘
select ‘ + QUOTENAME([Name],””) + ‘ as DBName, [AGGREGATE_FUNCTION], [CHECK_CONSTRAINT],[DEFAULT_CONSTRAINT],

[FOREIGN_KEY_CONSTRAINT],

[SQL_SCALAR_FUNCTION],

[CLR_SCALAR_FUNCTION],

[CLR_TABLE_VALUED_FUNCTION],

[SQL_INLINE_TABLE_VALUED_FUNCTION],

[INTERNAL_TABLE],[SQL_STORED_PROCEDURE],[CLR_STORED_PROCEDURE],[PLAN_GUIDE],[PRIMARY_KEY_CONSTRAINT],
[RULE],[REPLICATION_FILTER_PROCEDURE],[SYNONYM],[SERVICE_QUEUE],[CLR_TRIGGER],[SQL_TABLE_VALUED_FUNCTION],[SQL_TRIGGER],
[TABLE_TYPE],[USER_TABLE],[UNIQUE_CONSTRAINT],[VIEW],[EXTENDED_STORED_PROCEDURE]

from (select [Name], type_Desc from ‘ + QUOTENAME([Name]) + ‘.sys.objects where is_ms_shipped = 0) src
PIVOT (count([Name]) FOR type_desc in ([AGGREGATE_FUNCTION], [CHECK_CONSTRAINT],[DEFAULT_CONSTRAINT],

[FOREIGN_KEY_CONSTRAINT],

[SQL_SCALAR_FUNCTION],

[CLR_SCALAR_FUNCTION],

[CLR_TABLE_VALUED_FUNCTION],

[SQL_INLINE_TABLE_VALUED_FUNCTION],

[INTERNAL_TABLE],[SQL_STORED_PROCEDURE],[CLR_STORED_PROCEDURE],[PLAN_GUIDE],[PRIMARY_KEY_CONSTRAINT],
[RULE],[REPLICATION_FILTER_PROCEDURE],[SYNONYM],[SERVICE_QUEUE],[CLR_TRIGGER],[SQL_TABLE_VALUED_FUNCTION],[SQL_TRIGGER],
[TABLE_TYPE],[USER_TABLE],[UNIQUE_CONSTRAINT],[VIEW],[EXTENDED_STORED_PROCEDURE])) pvt’ FROM sys.databases
WHERE [name] NOT IN (‘master’,’tempdb’,’model’,’msdb’) ORDER BY [Name]

EXECUTE(@Qry)
————————————————————————-
–Quick Record Count in All Tables in All Databases
DECLARE @SQL NVARCHAR(MAX)

SET @SQL = ”
–select * from sys.databases
SELECT @SQL = @SQL + CHAR(13) + ‘USE ‘ + QUOTENAME([name]) + ‘;
SELECT ‘ +QUOTENAME([name],””) + ‘as [Database Name],
SchemaName=s.name
,TableName=t.name
,CreateDate=t.create_date
,ModifyDate=t.modify_date
,p.rows
,DataInKB=sum(a.used_pages)*8
FROM sys.schemas s
JOIN sys.tables t on s.schema_id=t.schema_id
JOIN sys.partitions p on t.object_id=p.object_id
JOIN sys.allocation_units a on a.container_id=p.partition_id
GROUP BY s.name, t.name, t.create_date, t.modify_date, p.rows
ORDER BY SchemaName, TableName’ FROM sys.databases

EXECUTE (@SQL)
————————————
DECLARE @SQL NVARCHAR(MAX)

SET @SQL = ”
–select * from sys.databases
SELECT @SQL = @SQL + CHAR(13) + ‘USE ‘ + QUOTENAME([name]) + ‘;
SELECT ‘ +QUOTENAME([name],””) + ‘as [Database Name], so.name AS [Table Name],
rows AS [RowCount]
FROM sysindexes AS si
join sysobjects AS so on si.id = so.id
WHERE indid IN (0,1)
AND xtype = ”U”’ FROM sys.databases

EXECUTE (@SQL)
————————————————————————————–
—All Schema Names in All Databases
use master
go
DECLARE @SQL NVARCHAR(MAX)
CREATE TABLE AllDBSchemas ([DB Name] sysname, [SCHEMA Name] sysname)

SELECT @SQL = COALESCE(@SQL,”) + ‘
insert into AllDBSchemas

select ‘ + QUOTENAME(name,””) + ‘ as [DB Name], [Name] as [Schema Name] from ‘ +
QUOTENAME(Name) + ‘.sys.schemas order by [DB Name],[Name];’ FROM sys.databases
ORDER BY name

EXECUTE(@SQL)

SELECT * FROM AllDBSchemas
–where [DB Name] = ‘FileStreamDB’ –Option To Select One Database Or More As you Like
ORDER BY [DB Name],[SCHEMA NAME]
drop table AllDBSchemas

——————————————————————————————-
–List of All Tables in All Databases
use MASTER
GO
CREATE TABLE AllTables ([DB Name] sysname, [SCHEMA Name] sysname, [TABLE Name] sysname)

DECLARE @SQL NVARCHAR(MAX)

SELECT @SQL = COALESCE(@SQL,”) + ‘
insert into AllTables

select ‘ + QUOTENAME(name,””) + ‘ as [DB Name], [Table_Schema] as [Table Schema], [Table_Name] as [Table Name] from ‘ +
QUOTENAME(Name) + ‘.INFORMATION_SCHEMA.Tables;’ FROM sys.databases
ORDER BY name

EXECUTE(@SQL)

SELECT * FROM AllTables
–WHERE [DB NAME] = ‘YOUR_DATABASE NAME’ — OPTION
ORDER BY [DB Name],[SCHEMA NAME], [TABLE Name]
drop table AllTables
—————————
-2
USE MASTER
GO
IF OBJECT_ID(‘TempDB..#AllTables’,’U’) IS NOT NULL DROP TABLE #AllTables
CREATE TABLE #AllTables ([DB Name] sysname, [SCHEMA Name] NVARCHAR(128) NULL, [TABLE Name] sysname, create_date DATETIME, modify_date DATETIME)

DECLARE @SQL NVARCHAR(MAX)

SELECT @SQL = COALESCE(@SQL,”) + ‘USE ‘ + QUOTENAME(name) + ‘
insert into #AllTables
select ‘ + QUOTENAME(name,””) + ‘ as [DB Name], schema_name(schema_id) as [Table Schema], [Name] as [Table Name], Create_Date, Modify_Date
from ‘ +
QUOTENAME(Name) + ‘.sys.Tables;’ FROM sys.databases
ORDER BY name
–print @SQL
EXECUTE(@SQL)
SELECT * FROM #AllTables
–WHERE [DB NAME] = ‘YOUR DATABASE NAME’ — OPTION
ORDER BY [DB Name],[SCHEMA NAME], [TABLE Name]
—————————————————————————————–
–LIST OF ALL procedures IN ALL DATABASES
IF OBJECT_ID(‘TempDB..#SPList’,’U’) IS NOT NULL DROP TABLE #SPList
CREATE TABLE #SPList ([DB Name] sysname, [SP Name] sysname,
create_date DATETIME, modify_date DATETIME)

DECLARE @SQL NVARCHAR(MAX)
SET @SQL = ”
SELECT @SQL = @SQL + ‘ insert into #SPList
select ‘ + QUOTENAME(name, ””) + ‘, name, create_date, modify_date
from ‘ + QUOTENAME(name) + ‘.sys.procedures’ FROM sys.databases

EXECUTE (@SQL)

SELECT * FROM #SPList
–WHERE [DB NAME] = ‘YOUR DATABASE NAME’ — OPTION
ORDER BY [DB Name], [SP Name]
————————————————————–
–LIST OF ALL VIEWS IN ALL DATABASES
IF OBJECT_ID(‘TempDB..#SPList’,’U’) IS NOT NULL DROP TABLE #SPList
CREATE TABLE #SPList ([DB Name] sysname, [SP Name] sysname,
create_date DATETIME, modify_date DATETIME)

DECLARE @SQL NVARCHAR(MAX)
SET @SQL = ”
SELECT @SQL = @SQL + ‘ insert into #SPList
select ‘ + QUOTENAME(name, ””) + ‘, name, create_date, modify_date
from ‘ + QUOTENAME(name) + ‘.sys.VIEWS’ FROM sys.databases

EXECUTE (@SQL)

SELECT * FROM #SPList
–WHERE [DB NAME] = ‘YOUR DATABASE NAME’ — OPTION
ORDER BY [DB Name], [SP Name]
——————————————————————————
–Size Tales in Database Return(Count Of Rows,Reserved Size,Data Size,Index Size,UnUsed Size)
exec sp_MSforeachtable ‘print ”?” exec sp_spaceused ”?”’
IF OBJECT_ID(‘tempdb..#TablesSizes’) IS NOT NULL
DROP TABLE #TablesSizes

CREATE TABLE #TablesSizes (TableName sysname, ROWS BIGINT, reserved VARCHAR(100), DATA VARCHAR(100), index_size VARCHAR(100), unused VARCHAR(100))

DECLARE @SQL VARCHAR(MAX)
SELECT @SQL = COALESCE(@SQL,”) + ‘
insert into #TablesSizes execute sp_spaceused ‘ + QUOTENAME(Table_Name,””) FROM INFORMATION_SCHEMA.TABLES

print (@SQL)
EXECUTE (@SQL)

SELECT * FROM #TablesSizes ORDER BY TableName
————————————————————————————————
–Database File Size (MDF.LDF.NDF)in ALL Databases
CREATE TABLE #FileSizes (DBName sysname, [FILE Name] VARCHAR(MAX), [Physical Name] VARCHAR(MAX),
SIZE DECIMAL(12,2))
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = ”
SELECT @SQL = @SQL + ‘USE’ + QUOTENAME(name) + ‘
insert into #FileSizes
select ‘ + QUOTENAME(name,””) + ‘, Name, Physical_Name, size/1024.0 from sys.database_files ‘
FROM sys.databases

EXECUTE (@SQL)
SELECT * FROM #FileSizes ORDER BY DBName, [FILE Name]
Drop Table #FileSizes
———————————————–
-2
use master
go
CREATE TABLE #FileSizes (DBName sysname, [FILE Name] VARCHAR(MAX), [Physical Name] VARCHAR(MAX),
SIZE DECIMAL(12,2))
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = ”
SELECT @SQL = @SQL + ‘USE’ + QUOTENAME(name) + ‘
insert into #FileSizes
select ‘ + QUOTENAME(name,””) + ‘, Name, Physical_Name, size/1024.0 from sys.database_files ‘
FROM sys.databases

EXECUTE (@SQL)
SELECT * FROM #FileSizes ORDER BY DBName, [FILE Name]
drop table #FileSizes
——————————————————————————————–
—Database SIZE
DECLARE @SQL1 VARCHAR(MAX)
SELECT @SQL1 =COALESCE(@SQL1 + CHAR(13) + ‘UNION ALL
‘ ,”) + ‘SELECT ”’ + name + ”’ AS DBNAME,’ +
‘sum(size * 8 /1024.0) AS MB from ‘ + QUOTENAME(name) + ‘.dbo.sysfiles’
FROM sys.databases
ORDER BY name

EXECUTE (@SQL1)
——————————-
–2
/*
The first select statement is to get how many kilobytes a data page has.
SQL Server allocates disk space in the unit of data page. Currently each
SQL server data page contains 8k bytes. The number of data pages allocated
to each database file is recorded in the sysfiles system table.
With this information on hand the script creates a temporary table #tem and
update the temporary table with size information which is gathered by querying the sysfiles table.
*/
use master

declare @PageSize varchar(10)
select @PageSize= v.low/1024.0
from master..spt_values v
where v.number=1 and v.type=’E’
–select * from master..spt_values v
select name as DatabaseName, convert(float,null) as Size
into #tem
From sysdatabases

declare @SQL varchar (8000)
set @SQL=”

while exists (select * from #tem where size is null)
begin
select @SQL=’update #tem set size=(select round(sum(size)*’+@PageSize+’/1024,0) From ‘+quotename(databasename)+’.dbo.sysfiles) where databasename=”’+databasename+””
from #tem
where size is null
exec (@SQL)
end

select * from #tem order by DatabaseName
drop table #tem

————————————————————————-
—Backup All Databases with Compression (SQL 2008)
DECLARE @ToExecute VARCHAR(8000)

SELECT @ToExecute = COALESCE(@ToExecute + ‘Backup Database ‘ + QUOTENAME([Name]) +
‘ To Disk = ”E:\Backups\’ + [Name] + ‘.bak”
WITH NOFORMAT, NOINIT,
SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10’ + CHAR(13),”)

FROM sys.databases

WHERE [Name] Not In (‘tempdb’) and DATABASEPROPERTYEX ([Name],’Status’) = ‘online’

EXECUTE (@ToExecute)
———————————————————————————————