filegroup get all DB tables and views info

-- Get all DB tables and views filegroup info
SET NOCOUNT ON

-- This table will hold the collected filegroup information
IF OBJECT_ID('tempdb..#tblFileGroupInfo', 'U') IS NOT NULL
DROP TABLE #tblFileGroupInfo

CREATE TABLE #tblFileGroupInfo(
TableOrViewName NVARCHAR(128),
[Table/View] VARCHAR(16),
AttributeName NVARCHAR(128),
AttributeType VARCHAR(128),
AttributeIndid INT,
FileGroupName NVARCHAR(128),
FileGroupID INT)


-- Get the file group information for all indexes of all tables and indexedviews
-- on the database. Note that auto-statistics created by SQL server are excluded,
-- since they are hardly of interest.
INSERT INTO #tblFileGroupInfo(
TableOrViewName,
[Table/View],
AttributeName,
AttributeType,
AttributeIndid,
FileGroupName,
FileGroupID)
SELECT a.TABLE_NAME,
CASE WHEN a.TABLE_TYPE = 'VIEW' THEN 'View'
ELSE 'Table'
END,
ISNULL(b.[name], 'NA'),
CASE WHEN b.indid = 0 THEN 'Table Row Data (table has no
clustered index)'
WHEN b.indid = 1 THEN 'Clustered Index (and table row data)'
WHEN b.indid = 255 THEN 'TEXT/NTEXT/IMAGE/XML Column Data'
ELSE 'Non Clustered Index'
END,
b.indid,
FILEGROUP_NAME(b.groupid),
b.groupid
FROM INFORMATION_SCHEMA.TABLES a WITH (NOLOCK)
INNER JOIN sysindexes b WITH (NOLOCK)
ON OBJECT_ID(a.TABLE_NAME) = b.[id]
WHERE INDEXPROPERTY(b.[id], b.[name], 'IsAutoStatistics') = 0


SET NOCOUNT OFF

SELECT *
FROM #tblFileGroupInfo WITH (NOLOCK)
ORDER BY TableOrViewName ASC, AttributeIndid ASC

-- Cleanup

IF OBJECT_ID('tempdb..#tblFileGroupInfo', 'U') IS NOT NULL
DROP TABLE #tblFileGroupInfo
GO


----------------------
--another simple way--
----------------------
-- to identofy which tables and indexes are used in which filegroup
select distinct(object_name(id)) from sysindexes where groupid=filegroup_id('Third')
select name from sysindexes where groupid=filegroup_id('Third') and indid > 0

Filegroup space usage and allocation

create table #Data(
FileID int NOT NULL,
[FileGroupId] int NOT NULL,
TotalExtents int NOT NULL,
UsedExtents int NOT NULL,
[FileName] sysname NOT NULL,
[FilePath] varchar(max) NOT NULL,
[FileGroup] varchar(MAX) NULL
)

create table #Results(
db sysname NULL ,
FileType varchar(4) NOT NULL,
[FileGroup] sysname not null,
[FileName] sysname NOT NULL,
TotalMB numeric(18,2) NOT NULL,
UsedMB numeric(18,2) NOT NULL,
PctUsed numeric(18,2) NULL,
FilePATH nvarchar(MAX) NULL,
FileID int null
)

create table #Log(
db sysname NOT NULL,
LogSize numeric(18,5) NOT NULL,
LogUsed numeric(18,5) NOT NULL,
Status int NOT NULL,
[FilePath] varchar(max) NULL
)

INSERT #Data (Fileid , [FileGroupId], TotalExtents ,
UsedExtents , [FileName] , [FilePath])
EXEC ('DBCC showfilestats')

update #Data
set #data.Filegroup = sysfilegroups.groupname
from #data, sysfilegroups
where #data.FilegroupId = sysfilegroups.groupid

INSERT INTO #Results (db ,[FileGroup], FileType , [FileName], TotalMB ,
UsedMB , PctUsed , FilePATH, FileID)
SELECT DB_NAME() db,
[FileGroup],
'Data' FileType,
[FileName],
TotalExtents * 64./1024. TotalMB,
UsedExtents *64./1024 UsedMB,
UsedExtents*100. /TotalExtents UsedPct,
[FilePath],
FileID
FROM #Data
order BY 1,2

insert #Log (db,LogSize,LogUsed,Status )
exec('dbcc sqlperf(logspace)')

insert #Results(db, [FileGroup], FileType, [FileName], TotalMB,UsedMB, PctUsed, FilePath, FileID)
select DB_NAME() db,
'Log' [FileGroup],
'Log' FileType,
s.[name] [FileName],
s.Size/128. as LogSize ,
FILEPROPERTY(s.name,'spaceused')/8.00 /16.00 As LogUsedSpace,
(s.Size/128. - FILEPROPERTY(s.name,'spaceused')/8.00 /16.00) UsedPct,
s.FileName FilePath,
s.FileId FileID
from #Log l , master.dbo.sysaltfiles f , dbo.sysfiles s
where f.dbid = DB_ID()
and (s.status & 0x40) <> 0
and s.fileid=f.fileid
and l.db = DB_NAME()

SELECT r.*,
CASE WHEN s.maxsize = -1 THEN null
else CONVERT(decimal(18,2), s.maxsize /128.)
END MaxSizeMB,
CONVERT(decimal(18,2), s.growth /128.) GrowthMB
FROM #Results r
INNER JOIN dbo.sysfiles s
ON r.FileID = s.FileID
ORDER BY 1,2,3,4,5

DROP TABLE #Data
DROP TABLE #Results
DROP TABLE #Log

SQL server performance trouble shooting tools

http://sqlserver-qa.net/blogs/tools/archive/2008/06/05/sql-server-troubleshooting-tools-pssdiag-sqldiag-sqlnexus-rml-utilities-and-readtrace-which-one-to-choose.aspx

Bunch of articles for SQL performace

http://www.simple-talk.com/sql/performance/

Finding the Causes of Poor Performance in SQL Server

Finding the Causes of Poor Performance in SQL Server, Part 1

Finding the Causes of Poor Performance in SQL Server, Part 2

Add "Command Prompt Here" Shortcut to Windows Explorer

Through the Registry

Navigate in your Registry to
HKEY_LOCAL_MACHINE/Software/Classes/Folder/Shell
and create a key called "Command Prompt" without the quotes.

Set the default string to whatever text you want to appear in the right-click menu.
Create a new key within your newly created command prompt named "command," and set the default string to
cmd.exe /k pushd %1
You may need to add %SystemRoot%\system32\ before the cmd.exe if the executable can't be found.

The changes should take place immediately. Right click a folder and your new menu item should appear.

Clean Duplicate records

DECLARE @Count int
DECLARE @name nvarchar(50)
DECLARE @culture_name nvarchar(50)

DECLARE dublicate_cursor CURSOR FAST_FORWARD FOR
SELECT name, culture_name , Count(*) - 1
FROM resource
GROUP BY name, culture_name
HAVING Count(*) > 1

OPEN dublicate_cursor

FETCH NEXT FROM dublicate_cursor INTO @name, @culture_name, @Count

WHILE @@FETCH_STATUS = 0
BEGIN

SET ROWCOUNT @Count
DELETE FROM resource WHERE name = @name AND culture_name = @culture_name
SET ROWCOUNT 0

FETCH NEXT FROM dublicate_cursor INTO @name, @culture_name, @Count
END

CLOSE dublicate_cursor
DEALLOCATE dublicate_cursor

Find the table column order not matching

if(OBJECT_ID('tempdb..#tmp')>1)
drop table #tmp

create table #tmp (database_name nvarchar(100), table_name nvarchar(100), column_name nvarchar(100), ordinal_position int)


EXEC master..sp_MSForEachDB '
if(''?'' not in (''master'',''msdb'',''tempdb'',''model''))
begin
use [?]
insert into #tmp
select db_name(), table_name , column_name , ordinal_position from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = ''users''
end
'
go

select * from #tmp;

select *
from #tmp t1
inner join #tmp t2 on t1.column_name = t2.column_name
where t1.ordinal_position <> t2.ordinal_position

A simple way to loop true the time counter

startme:
print convert(nvarchar(100),GETDATE(),109);
waitfor delay '00:00:01';

if(GETDATE() > '2010-03-24 15:18:00.713')
begin
goto endme;
end
else
begin
goto startme;
end

endme:

Displaying Execution Plans

Check the query IO, TIME, Execution Plan, Plan Text while running a query

Displaying Execution Plans1