How To identify Unused Tables and stored procedures

How To identify Unused Tables and stored procedures

-- Unused tables & indexes. Tables have index_id’s of either 0 = Heap table or 1 = Clustered Index

SELECT OBJECTNAME = OBJECT_NAME(I.OBJECT_ID), INDEXNAME = I.NAME, I.INDEX_ID

FROM SYS.INDEXES AS I

INNER JOIN SYS.OBJECTS AS O

ON I.OBJECT_ID = O.OBJECT_ID

WHERE OBJECTPROPERTY(O.OBJECT_ID,'IsUserTable') = 1

AND I.INDEX_ID

NOT IN (SELECT S.INDEX_ID

FROM SYS.DM_DB_INDEX_USAGE_STATS AS S

WHERE S.OBJECT_ID = I.OBJECT_ID

AND I.INDEX_ID = S.INDEX_ID

AND DATABASE_ID = DB_ID(db_name()))

ORDER BY OBJECTNAME, I.INDEX_ID, INDEXNAME ASC

How To identify Unused Tables and stored procedures

How To identify Unused Tables and stored procedures

-- Unused tables & indexes. Tables have index_id’s of either 0 = Heap table or 1 = Clustered Index

SELECT OBJECTNAME = OBJECT_NAME(I.OBJECT_ID), INDEXNAME = I.NAME, I.INDEX_ID

FROM SYS.INDEXES AS I

INNER JOIN SYS.OBJECTS AS O

ON I.OBJECT_ID = O.OBJECT_ID

WHERE OBJECTPROPERTY(O.OBJECT_ID,'IsUserTable') = 1

AND I.INDEX_ID

NOT IN (SELECT S.INDEX_ID

FROM SYS.DM_DB_INDEX_USAGE_STATS AS S

WHERE S.OBJECT_ID = I.OBJECT_ID

AND I.INDEX_ID = S.INDEX_ID

AND DATABASE_ID = DB_ID(db_name()))

ORDER BY OBJECTNAME, I.INDEX_ID, INDEXNAME ASC

Update Satatistics in sql server 2005

It's very easy way to update statistics in sqlserver, There are several ways.
1.By creating Maintenance plan from maintenance wizard.
2.By setting a property on data base level, it is automatically updating.

First Way:
1.Choose Management
2.Then Choose Maintenance palns
3.Then Right click on 'Maintenance palns'
4.Choose New paln option
5.Then it shows controls for Update statistics
6.Drag update statistics control to the control area
7.Set data base coneection
8.Set which data base you want to update statistics
9.Click on OK
10.Choose .... button for Scheduling Job
11.Define when it is going to be run
12.It's better to run by sundays :)
13.Then save that maintenance paln with desired name
14.Check the job is scheduled or not in Job Activity monitor
15.There we can find our maintenance paln
16.Then the fun starts on sundays, If we set that job on sundays

hahahahaha.....

Second Way:

1.Choose data base
2.Right click on the desired data base.
3.Choose properties
4.Then choose options
5.Set Auto create statistics property is True
6.Set Auto update statistics property is True
7.Close the window

Haaha this is very easy.......

Find weak login passwords in your server

Have you ever tried to search for all logins in your SQL Server who are using weak passwords?
You can create a table to contain weak password list instead of using table variable in the script and convert this script into a stored procedure to be able to use more frequently.
The method in the script can be used to check weak passwords in your user tables where they contain user names and hashed password.
I tried this script in the server I'm working on and found 3 logins containing weak passwords. Of course, I told them to change immediately. :)

Script:
DECLARE @WeakPwdList TABLE(WeakPwd NVARCHAR(255))
--Define weak password list
--Use @@Name if users password contain their name
INSERT INTO @WeakPwdList(WeakPwd)
SELECT ''UNION SELECT '123'
UNION SELECT '1234'
UNION SELECT '12345'
UNION SELECT 'abc'
UNION SELECT 'default'
UNION SELECT 'guest'
UNION SELECT '123456'
UNION SELECT '@@Name123'
UNION SELECT '@@Name'
UNION SELECT '@@Name@@Name'
UNION SELECT 'admin'
UNION SELECT 'Administrator'
UNION SELECT 'admin123'
-- SELECT * FROM @WeakPwdList

SELECT t1.name [Login Name], REPLACE(t2.WeakPwd,'@@Name',t1.name) As [Password]FROM sys.sql_logins t1 INNER JOIN @WeakPwdList t2 ON (PWDCOMPARE(t2.WeakPwd, password_hash) = 1 OR PWDCOMPARE(REPLACE(t2.WeakPwd,'@@Name',t1.name),password_hash) = 1)

Update statistics in all databases

Update statistics in all databases:
USE master
go
exec sp_MSforeachdb ' IF (''?'' NOT IN (''master'',''tempdb'',''model'',''msdb'',''ReportServerTempDB''))BEGIN PRINT ''Atualizando estatísticas de '' + ''?''use ? exec sp_updatestats END';

HowToUseDataProfiling in Sql server 2008, DTA tool with video

How To Use Data Profiling, Watch this video, It's free.....!

HowToCallWebService in Sqlserver 2008, Video

HowToCallWebService in Sqlserver 2008, Video

replace special charcters in phone number

replace special charcters in phone number

update T_profiles set profile_phone1=replace(replace(replace(replace(profile_phone1,'-',''),'',''),')',''),'(','')

where profile_id

Killing All Sqlserver databases

Declare @DBName varchar(50), @withmsg bit
Set @withmsg=1
Set @DBName='yourdatabasenmame' ---Change your databasename here
SET NOCOUNT ON
DECLARE @spidstr varchar(8000)
DECLARE @ConnKilled smallint
SET @ConnKilled=0
SET @spidstr = ''
IF db_id(@DBName) < spidstr="coalesce(@spidstr,','" dbid="db_id(@DBName)"> 0
BEGIN
EXEC(@spidstr)
SELECT @ConnKilled = COUNT(1)
FROM master..sysprocesses WHERE dbid=db_id(@DBName)
END
IF @withmsg =1
PRINT CONVERT(VARCHAR(10), @ConnKilled) + ' Connection(s) killed for DB ' + @DBName
GO

Note: Change the databasename for @DBName variable

combine rows with single column in Sql Server 2005

combine rows with single column--Query to combine multiple rows into one
DECLARE @str VARCHAR(100)
SELECT @str = COALESCE(@str + ',', '') + [ct]
FROM #cat1
Print @str

Here [ct] is column name that we have rows of data. Then finally entire rows of data is avilable in @str.

Example:
ct
--------
suresh
hemanth
kareem
malli

after processing with COALESCE then the output loks like.....
----------------------------------
suresh,hemanth,kareem,malli