In 60 Seconds

I'm really impressed at how well people are visualizing data theses days.
Below is another great example, it makes you want to read each fact.
I have to find a way to improve how I do the same, if I can get anywhere near this level I'll be very happy.

In 60 Seconds infographic

Row Counts for each table on Database from Metadata

Im sure a lot of people do this already but for those of you who dont there is an easy way to find the row counts of all of your tables without having to wait for count(*) commands.

SQL Server saves this information for you.

Just run the below on each database you want to check OR if you want you can use the sp_foreachDB I mentioned in a previous post (Run SQL On Each Database) to run it on multiple databases. If I was to do that I would pump the data into a temp table each time and then return the results from it.

SELECT
 DB_NAME() as DatabaseName, 
        st.Name,
 sc.Name as ''Schema'',
        SUM(CASE WHEN (p.index_id < 2) AND (a.type = 1) THEN p.rows 
            ELSE 0 END) AS Rows,
 st.Modify_Date
FROM sys.partitions p
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
INNER JOIN sys.tables st ON st.object_id = p.Object_ID
INNER JOIN sys.schemas sc on st.schema_id = sc.schema_id
GROUP BY st.Name, sc.Name, st.Modify_Date
ORDER BY rows desc, sc.Name, st.Name

Big Data analysis allows businesses and governments to mine your personal details

A good article on what some organisations are doing in the way of analytics. Whenever you provide any information to some one or interact in a way where they know who you are even if it is just with a credit card they can track you.

Big Data analysis allows businesses and governments to mine your personal details

SSMS Tools Pack

If you haven't already got SSMS Tools Pack I highly recommend it.

I use it a lot for the SQL Snippets which allows short cuts for example stf<ENTER> will return Select Top 100 FROM in the query window. All the Snippets are customisable and you create your own.

Running Custom Scripts from the Object Explorer is great too, I have scripts I want to run at a server level so I just click on the Server in Object Explorer and then just choose the script I want to run.

A lot of other really useful tools too, just makes life that little bit easier.

Sourcing Data from Active Directory

Something I've found useful in the past is being able to source user details from Active Directory.
Its nice being able to link the User Names from your systems up to actual Names of the users when providing data.
It is also handy when you want to find which users have certain security access. For example who has access to Database X.

/*
--Check if Ad Hoc Distributed Queries is Visible/Turned on
sp_configure

--Make Visible if it isnt
sp_configure 'show advanced options', 1
reconfigure

--Turn it on
sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure
*/

--Return All Active Directory Users

--Replace ABC.DEF with your active directory server name

select
*
FROM OPENROWSET('ADSDSOObject',
'adsdatasource;', 'SELECT Title, Department, Mail, DisplayName, Sn, GivenName, Cn
FROM ''LDAP://ABC.DEF'' where objectClass = ''User'' AND objectClass<>''computer'' '
)

--Find All Active Directory Groups

--Replace ABC.DEF with your active directory server name

select
*, substring(AdsPath, charindex('CN=', adspath), 300) as GroupName
,
substring(AdsPath, 0, charindex('CN=', adspath) - 1) as Domain
FROM
OPENROWSET('ADSDSOObject', 'adsdatasource;',
'SELECT AdsPath, name FROM ''LDAP://ABC.DEF'' WHERE objectCategory=''Group'' '
)

--Find Users from X Active Directory Group

--Replace 'YourADGroup' with the name of the Group you are after

--Replace ABC.DEF with your active directory server name
--Also again in the DC=ABC,DC=DEF section

select
*
FROM OPENROWSET('ADSDSOObject',
'adsdatasource;', 'SELECT Cn
FROM ''LDAP://ABC.DEF'' WHERE memberOf=''CN=YourADGroup,OU=Security,OU=Groups,DC=ABC,DC=DEF'' '
)

The Global State of Mobile Marketing

I wonder how the surge of Android phones and the release of Windows 8 will impact these figures over the next 12 months.


Embedded from ExactTarget