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)