August 21, 2015 at 9:38 am
Here is a script I use to grant (or request for myself) "power user" permissions without the need for full sysadmin membership.
-- server level permissions:
use master
-- grant user permission to view object schemas:
grant view any definition to [poweruser];
-- grant user permission to view system tables and views:
grant view server state to [poweruser];
-- grant user permission to start sql profiler traces:
grant alter trace to [poweruser];
GO
-- database level permissions:
use [thedatabase];
-- grant select on any table or view:
exec sp_addrolemember db_datareader, [poweruser]
-- grant user permission to view execution plans:
grant showplan to [poweruser];
GO
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
August 21, 2015 at 9:45 am
You'd think that folks would know better, but having dealt with a large number of development, UAT, and production servers; this script actually returns some surprises on occasion. It may even be good to include this in a scheduled job or maintenace plan with an email alert.
-- Query accounts with empty password:
select @@servername servername, name, '' as pwtext, type_desc, create_date
, modify_date, is_disabled, is_policy_checked, is_expiration_checked
from sys.sql_logins
where pwdcompare('', password_hash) = 1;
-- Query accounts where password = account name:
select @@servername servername, name, name as pwtext, type_desc, create_date
, modify_date, is_disabled, is_policy_checked, is_expiration_checked
from sys.sql_logins
where pwdcompare(name, password_hash) = 1;
-- There are several frequently used password lists posted on the web.
-- Here are a few, but perhaps 100 or more could be inserted here.
declare @PW table (pwtext varchar(180) not null primary key);
insert into @PW (pwtext) values
('sa'),('dev'),('prod'),('admin'),('admin1'),('administrator')
,(''),('password'),('123456'),('12345678'),('1234'),('qwerty');
select @@servername servername, name, pw.pwtext, type_desc, create_date
, modify_date, is_disabled, is_policy_checked, is_expiration_checked
from sys.sql_logins l
join @PW pw on pwdcompare(pw.pwtext, l.password_hash) = 1;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
August 21, 2015 at 10:12 am
Here are mine
sp_help 'tablename'
sp_spaceused 'tablename'
select si.name, si.is_disabled, iu.* from sys.dm_db_index_usage_stats iu join sys.indexes si on iu.object_id = si.object_id and iu.index_id = si.index_id where iu.object_id = object_id ('tablename')
dbcc show_statistics ('tablename','indexname')
dbcc sqlperf(logspace)
August 22, 2015 at 12:10 am
sp_helpdb
Restore filelistonly
Restore/backup database
Set statistics io/time on
sp_whoisactive
MCSE Data Platform; BI
MCITP Database Developer; BI
August 22, 2015 at 2:13 am
sp_helptext
set statistics io on
set statistics time on
dbcc checkdb
set transaction isolation level read uncommitted
August 22, 2015 at 3:57 am
Here are the ones I think I use the most:
1:
BEGIN TRAN;
go
go
ROLLBACK TRAN;
go
(after that, I type something in between the two "go" lines. But I always start with this and then execute to ensure there are no syntax errors)
2:
SET STATISTICS IO ON;
3:
SET STATISTICS TIME ON;
4:
sp_who2;
Okay, not quite five, but close.
August 22, 2015 at 11:30 am
1.
BEGIN TRY
--
--
END TRY
BEGIN CATCH
--
--
END CATCH
2.
BEGIN TRAN
--
--
COMMIT TRAN/ROLLBACK TRAN
3.
BACKUP DATABASE ----
TO D:\BACKUP\---- ;
4.
SET TRANSACTION ISOLATION LEVEL ----
5.
USE PLAYPEN
edit: Number 5 is maybe the one I use most. Number 2 next.
Tom
August 22, 2015 at 10:19 pm
To be honest, there's only one thing that I type very much and that's BEGIN TRANSACTION. As a DBA, I have pretty much everything else automated. I've even automated my "sp_ShowWorst" stored procedure, which keeps track of the top 20 consumers of CPU, IO, and several other categories every hour of the day. Other than my login information, the only other thing that I "type" on a regular basis is to run my "sp_WhatsRunning" proc. But, I don't type that either. I've got that bad boy hot-keyed.
--Jeff Moden
August 24, 2015 at 12:37 am
exec master..xp_fixeddrives
select * from sys.database_files
backup database DatabaseName to disk = 'FileLocation' with init, compression, copy_only, stats = 10
restore database DatabaseName from disk = ''
with
move 'Data' to 'FileLocation'
,move 'Log' to 'FileLocation'
,replace
,recovery
,stats = 10
-- Less overhead than activity monitor
select
...
from
sys.dm_exec_sessions es
left join sys.dm_exec_requests er on es.session_id = er.session_id
cross apply sys.dm_exec_sql_text(er.sql_handle) st
where
er.wait_time > 0
and es.session_id is not null
August 24, 2015 at 5:30 am
Used to generate a large number of rows.
L0..L9 are used to generate a large number of rows.
The lower parts are used to generate a specific 'code' in this case a number plate.
With a simple replacement of the statements it is fairly easy to generate a codes in a desired format. (Cijfer stands for a single digit, Letter stands for a single Character).
The top part of the code is used to generate a lot of rows, for many different situations.
The bottom part of the code is adjusted for each requirement.
WITH
L0 AS(SELECT 0 AS c UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0), -- 4
L1 AS(select 0 as x from L0 A, L0 B, L0 C, L0 D), -- 4 ^4 = 256
L2 AS(select 0 as x from L1 A, L1 B, L1 C, L1 D), -- (4 ^ 4) ^4 = 4 Giga
L9 AS(Select row_number() OVER(PARTITION BY 1 order by x ) as P from L2) -- voeg rijnummers toe
,
Letter0 as (Select CHAR(P-1+ASCII('A')) as P FROM L9 where P <= 26),
Letter as (SELECT * FROM Letter0 WHERE P NOT IN ('A','O','I','U','E','Q')),
Cijfer as (Select CHAR(P-1+ASCII('0')) as P FROM L9 where P <= 10),
plate as (select a.P+b.P+'--'+c.P+d.P+'--'+e.P+f.P plate_number,
ROW_NUMBER() over(partition by 1 ORDER BY (SELECT NULL)) volg
from cijfer a,cijfer b,letter c,letter d,cijfer e,cijfer f)
SELECT TOP 100 * FROM plate where volg > 1000000 and volg <= 1000333
A second one to determine differences between two tables:
select * from A
except
select * from B
Sometimes this is done on temp tables or copies of tables, to see what has changed during some time. The order of selects is sometimes swapped as wel to see the differences in the other direction.
A third one:
exec sp_drop A -- to drop a table if the table exists.SP_Drop is coded in the master table and when the table exists the table gets dropped.
This works for tables in the current database, for #tables and for ##tables.
If the table does not exist no action is taken
Ben
August 24, 2015 at 5:53 am
I will add some others later, but here's one I frequently use for our non-prod environments.
SELECT TOP 10 *
FROM msdb.dbo.restorehistory
WHERE destination_database_name = 'MyDatabase'
ORDER BY restore_date DESC;
--check the last time a certain non-prod database restore was done
USE MyDatabase;
GO
SELECT Table_Name, Column_Name
FROM INFORMATION_SCHEMA.Columns
WHERE Column_Name LIKE 'MyColumn%'
ORDER BY Table_Name, Column_Name;
--Find all tables containing a certain column name
EDIT: This is a recent script that is not complete. There are still a few datatypes I need to add to it in order to find the sizes. But it works fairly well for what I currently use with temptables.
DECLARE @TableName VARCHAR(50);
SET @TableName ='tempdb..#MyTempTable'; -- change this to your table name
SELECT c.name + ', ' AS ColumnName, c.name + ' ' + UPPER(t.name) +
CASE WHEN t.name IN ('VARCHAR','CHAR')
THEN '('+CAST(c.max_length AS VARCHAR(3) )+'),'
WHEN t.name IN ('DECIMAL','NUMERIC','FLOAT')
THEN '('+CAST(c.precision AS VARCHAR(3) )+','
+ CAST(c.scale AS VARCHAR(3) )+'),'
ELSE ',' END AS ColumnDataType
FROM tempdb.sys.columns c
INNER JOIN sys.types AS t ON c.system_type_id = t.system_type_id
WHERE object_id = object_id(@TableName )
ORDER BY c.column_id
--For discovering column datatypes and sizes on SELECT INTO created temp tables
Ordering the results of sp_who2.
--NOTE: This is NOT my script. I got it off StackOverflow
--http://stackoverflow.com/questions/2234691/sql-server-filter-output-of-sp-who2
--The WHERE Clause and ORDER BY is all mine, though.
DECLARE @Table TABLE(
SPID INT,
Status VARCHAR(MAX),
LOGIN VARCHAR(MAX),
HostName VARCHAR(MAX),
BlkBy VARCHAR(MAX),
DBName VARCHAR(MAX),
Command VARCHAR(MAX),
CPUTime INT,
DiskIO INT,
LastBatch VARCHAR(MAX),
ProgramName VARCHAR(MAX),
SPID_1 INT,
REQUESTID INT
)
INSERT INTO @Table EXEC sp_who2
SELECT * FROM @Table
/* The WHERE clause is used to filter results
by HostNames or Login names or Database names, etc. */
--WHERE -
-ProgramName LIKE 'Microsoft%'
--LOGIN = 'Domain\Login'
--HostName = 'MyServerName'
--DBName LIKE 'Report%'
ORDER BY BlkBy, SPID
-- I use BlkBy and SPID when looking for blocked processes
--otherwise, I order by DBName, etc.
And here's a good one for checking progress on restores, backups, and database shrinks:
SELECT percent_complete, blocking_session_id, *
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_os_tasks t
ON r.session_id = t.session_id
WHERE r.session_id = 138; --change this SPID as necessary
August 24, 2015 at 5:54 am
1. begin transaction
2. set statistics time, io on
3. select t.N from util.dbo.Tally1K (a 1000-row tally table available to everyone)
4. dateadd(day, 0, datediff(day, 0, getdate())) --many times on a column instead
5. backup database
Honestly, I have a great many frequently-used things automated. Things like getting the space allocated versus used, restoring a database from a specific backup file that I copied to another server, finding permissions for a specific login, etc. are all automated so they're always available and repeatable. Maybe a section of the article on automating things you do frequently would be too much scope creep.
I hope this gives you some help for your article. I look forward to reading it.
August 24, 2015 at 5:55 am
sp_whoisactive
sp_who2
sp_lock
August 24, 2015 at 6:21 am
These would be for when I'm working on client systems.
In no particular order:
SELECT * FROM sys.dm_exec_requests er CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) st;
SELECT TOP(1000) * FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY total_worker_time DESC; -- or total elapsed time, or total logical reads
SELECT * FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC; -- quick and dirty
SELECT * FROM sys.configurations
ORDER BY name;
SELECT * FROM sys.databases;
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 24, 2015 at 8:23 am
-- Variations on the following frequently including sys.dm_exec_sql_text and sys.dm_exec_query_plan
SELECT blocking_session_id,* FROM sys.dm_exec_requests WHERE session_id > 50
EXECUTE AS LOGIN = 'AD Login'
EXEC sp_helpindex [tablename]
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
Viewing 15 posts - 16 through 30 (of 63 total)
You must be logged in to reply to this topic. Login to reply