SQL Server : Guest user status on all databases

Below script will only list the DB's where guest user is enabled.

create table #guest_users

(

ServerName nvarchar(50),

DBName nvarchar(200),

name sysname,

Status nvarchar(50)

)

EXEC sp_MSforeachdb

'USE [?];

Insert into #guest_users

SELECT    @@Servername AS ServerName,

DB_NAME() AS DBName,

name,

case hasdbaccess when 1 then ''Enabled'' else ''Disabled'' end AS Status

FROM sysusers where name like ''guest'' AND hasdbaccess = 1;'

Select * from #guest_users

drop table #guest_users

--- Below script will list the DB's where guest user is enabled/disabled with value 

CREATE TABLE #guest_users

(

       ServerName nvarchar(50),

       DBName nvarchar(200),

       name sysname,

       Status nvarchar(50)

)

EXEC sp_MSforeachdb

'USE [?];

Insert into #guest_users

SELECT @@Servername AS ServerName,

             DB_NAME() AS DBName,

             name,

             case hasdbaccess when 1 then ''Enabled'' else ''Disabled'' end AS Status

       FROM sysusers where name like ''guest'';'

SELECT * FROM #guest_users

DROP TABLE #guest_users

SQL Server :: T-SQL command that will determine / find the biggest tables in a database

It is very common to deal with situation where you have thousands of tables and want to find the biggest tables in your database. Following mentioned script will return row count, total consumed size per table so we can apply filters too to get only those table which satisfy our criteria ( for example , table which have more than 1 million rows) .

Please change database context accordingly.

**********************************************

SELECT

    t.NAME AS TableName,

    i.name as indexName,

    sum(p.rows) as RowCounts,

    sum(a.total_pages) as TotalPages,

    sum(a.used_pages) as UsedPages,

    sum(a.data_pages) as DataPages,

    (sum(a.total_pages) * 8) / 1024 as TotalSpaceMB,

    (sum(a.used_pages) * 8) / 1024 as UsedSpaceMB,

    (sum(a.data_pages) * 8) / 1024 as DataSpaceMB

FROM

    sys.tables t

INNER JOIN     

    sys.indexes i ON t.OBJECT_ID = i.object_id

INNER JOIN

    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id

INNER JOIN

    sys.allocation_units a ON p.partition_id = a.container_id

WHERE

    t.NAME NOT LIKE 'dt%' AND

    i.OBJECT_ID > 255 AND  

    i.index_id <= 1

GROUP BY

    t.NAME, i.object_id, i.index_id, i.name

ORDER BY

    (sum(a.data_pages) * 8) / 1024 desc

Hope it will help you .

Brgds,

Chhavinath Mishra

Sr. Database Administrator

Microsoft Certified IT Professional (MCITP)

SQL Server : T-SQL command that will determine in which filegroup, a particular table exist

It is very common to deal with situation where you have thousands of tables which are residing on multiple filegroups and you would like to know that in which filegroup, a particular table exist in your database.

Please change database table name accordingly.

**********************************************

/*Script for finding on which Filegroup the object resides in a database*/

USE Test

GO

SELECT object_name(i.[object_id]) as Name_of_Object,

i.name as Index_Name,

i.type_desc as Index_Type,

f.name as Name_of_Filegroup,

a.type as Object_Type,

f.type,

f.type_desc

FROM sys.filegroups as f

INNER JOIN sys.indexes as i

 ON f.data_space_id = i.data_space_id

INNER JOIN sys.all_objects as a

 ON i.object_id = a.object_id

WHERE a.type ='U' -- User defined tables only

AND object_name(i.[object_id]) ='employee' -- Specific object

GO

Hope it will help you .

Brgds,

Chhavinath Mishra

Sr. Database Administrator

Microsoft Certified IT Professional (MCITP)

SQL Server : Sample Scripts to create database, table & Index

It is very common to deal with situation where you have to create database and tables using T-SQL.

Here are the sample scripts for same which can be used as reference.

First check the data and log file location for any database if there is:

  

Use YourExistingDB

go

sp_helpfile

-- Create Dabase

use master

go

create database Test

on Primary (Name = test_data1, FileName = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012B\MSSQL\DATA\test_data1.mdf'),

Filegroup Test_Secondary

(Name = test_Data_2, FileName = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012B\MSSQL\DATA\test_Data_2.ndf'),

Filegroup Test_Archive

(Name = test_data3 , FileName = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012B\MSSQL\DATA\test_data2.ndf')

Log ON (Name = Test_Log, FileName = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012B\MSSQL\Log\test_log.ldf')

Go

-- Create Table

use Test

Go

Create Table dbo.Employee

(emp_id int,

emp_fname varchar (20),

emp_lname varchar (30))

on test_secondary

go

-- Create Index

use Test

Go
Create clustered index CIX_emp_Id on Test.dbo.Employee (emp_id)

Hope it will help you.

Brgds,

Chhavinath Mishra

Sr. Database Administrator

Microsoft Certified IT Professional (MCITP)

SQL Server : T-SQL command that will determine if a table exist or not

It is very common to deal with situation where you have thousands of tables and you would like to know that whether a particular table exist or not in your database.T-SQL command that will determine if a table exist or not. 

Please change database name and table name accordingly.

**********************************************

IF EXISTS

(SELECT *

FROM YourDB.INFORMATION_SCHEMA.TABLES

WHERE

TABLE_SCHEMA = 'dbo'

AND TABLE_NAME = 'YourTable'

AND TABLE_TYPE = 'BASE TABLE'

)

PRINT ' Table Exist'

Else

PRINT ' Table Does not Exist'

GO

**********************************************

Hope it will help you .

Brgds,

Chhavinath Mishra

Sr. Database Administrator

Microsoft Certified IT Professional (MCITP)

SQL Server : Get information about SQL Server instance like Version, Edition, Service pack , Collation and much more

In order to get information about SQL Server instance like Version, Edition, Service pack, Collation and much more , we have very useful System function SERVERPROPERTY. We can use the below mentioned T-SQL to get all these information:

SELECT 'ComputerName', SERVERPROPERTY('ComputerNamePhysicalNetBIOS')

UNION ALL
SELECT 'Edition', SERVERPROPERTY('Edition')
UNION ALL
SELECT 'ProductVersion', SERVERPROPERTY('ProductVersion')
UNION ALL
SELECT 'ProductLevel', SERVERPROPERTY('ProductLevel')
UNION ALL
SELECT 'IsClustered', SERVERPROPERTY('IsClustered')
UNION ALL
SELECT 'IsFullTextInstalled', SERVERPROPERTY('IsFullTextInstalled')
UNION ALL
SELECT 'IsIntegratedSecurityOnly', SERVERPROPERTY('IsIntegratedSecurityOnly')
UNION ALL
SELECT 'ResourceLastUpdateDateTime', SERVERPROPERTY('ResourceLastUpdateDateTime')
UNION ALL
SELECT 'ResourceVersion', SERVERPROPERTY('ResourceVersion')
UNION ALL
SELECT 'InstanceName', SERVERPROPERTY('InstanceName')
UNION ALL
SELECT 'Complete_InstanceName', SERVERPROPERTY('ServerName')
UNION ALL
SELECT 'Collation', SERVERPROPERTY('Collation')

Brgds,

Chhavinath Mishra

Database Administrator

Microsoft Certified IT Professional (MCITP)

SQL Server - Script to get Tables name, size and rows

SQL Server - Script to get Tables name, size and rows

Applicable on 2005/2008 & R2:

USE [DatabaseName]
GO

CREATE TABLE #temp (
table_name sysname ,
row_count INT,
reserved_size VARCHAR(50),
data_size VARCHAR(50),
index_size VARCHAR(50),
unused_size VARCHAR(50))
SET NOCOUNT ON
INSERT #temp
EXEC sp_msforeachtable 'sp_spaceused ''?'''
SELECT a.table_name,
a.row_count,
COUNT(*) AS col_count,
a.data_size
FROM #temp a
INNER JOIN information_schema.columns b
ON a.table_name collate database_default
= b.table_name collate database_default
GROUP BY a.table_name, a.row_count, a.data_size
ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC
DROP TABLE #temp

Brgds,

Chhavinath Mishra

Database Administrator

Microsoft Certified IT Professional (MCITP)