T-SQL query (on system tables) that what has changed in the database

select o.name, o.object_id, o.modify_date, c.text

from sys.objects o

join sys.syscomments c

on o.object_id = c.id

Aggregates with the Over Clause

You have likely heard the business term “Market Share”. If your company is the biggest and has sold 15 million units in an industry that has sold a total of 50 million units then your company’s market share is 30% (15/50 = .30). Market share represents your number divide by the sum of all other numbers. The two simple queries in the figure below show all the Grant table records and the sum of the grant amounts.

If we want to show the total amount next to every record of the table – or just one record of the table – SQL Server gives us the same error. It does not find the supporting aggregated language needed to support the SUM( ) aggregate function.

Adding the OVER( ) clause allows us to see the total amount next to each grant. We see 193,700 next to each record in the result set.

 

The sum of all 10 grants is $193,700. Recall the largest single grant (007) is $41,000. Doing the quick math in our head, we recognize $41,000 is around ¹/₅ of ~$200,000 and guesstimate that Grant 007 is just over 20% of the total.

Thanks to the OVER clause, there’s no need to guess. We can get the precise percentage. To accomplish this, we will add an expression that does the same math we did in our head. We want the new column to divide each grant amount by $193,700 (the total of all the grants).

By listing the total amount of all grants next to each individual grant, we automatically get a nice reference for how each individual grant compares to the total of all JProCo grants. The new column is added and confirms our prediction that Grant 007 represents just over 21% of all grants.

Notice that the figures in our new column appear as ratios. Percentages are 100 times the size of a ratio. Example:  the ratio 0.2116 represents a percentage of 21.16%. Multiplying a ratio by 100 will show the percentage. To finish, give the column a descriptive title, PercentOfTotal.

Wildcard Basics

Wildcard ranges

If you have ever been to a convention where they have a morning registration desk that must handle thousands of people in a short time you know they must put some pre-planning thought into how to handle this burst of volume. In fact often they will have many registration desks running in parallel to make things run faster. The first registration desk might handle all customer last names starting from A to K. Desk 2 will handle names from L to Q and the third desk will handle from R to Z.

With my last name being Morelan I would naturally head to registration desk #2 knowing that desk has the list with my name on it and the other desks don’t. Now let’s say you are in charge of creating these three separate lists and sending them out to the right registration workers. You know how to sort but how can you separate this lists using wildcards?

Wildcard Basics Recap

Lets start off with something most of us know already. Most SQL folks understand the usefulness and power of the basic uses of wildcards. Using wildcards allows you to do pattern matches in a column. In this case our criteria does not want to use the = sign to find a pattern match. The operator that allows you to do approximate predicates is LIKE. The LIKE operator allows you to do special relative searches to filter your result set.

--Find all LastNames that start with the letter A
SELECT *
FROM Employee
WHERE LastName LIKE 'A%'

To find everyone whose last name starts with the letter B, you need “B” to be the first letter. After the letter B you can have any number of characters. Using B% in single quotes after the LIKE operator gets all last names starting with the letter B.

--Find all LastNames that start with the letter B
SELECT *
FROM Employee
WHERE LastName LIKE 'B%'


Wildcard ranges or set specifiers

If you want to find all LastName values starting with the letters A or B you can use two predicates in your WHERE clause. You need to separate them with the OR operator.

--Find all LastNames that start with the letter B
SELECT *
FROM Employee
WHERE LastName LIKE 'A%'
OR LastName LIKE 'B%'

Finding names beginning with A or B is easy. How about the registration desk example where want the names ranging from A-K? This works well until you want a range of A-K as in the example below:

--Find all LastNames ranging from A-K
SELECT *
FROM Employee
WHERE LastName LIKE 'A%'
OR LastName LIKE 'B%'
OR LastName LIKE 'C%'
OR LastName LIKE 'D%'
OR LastName LIKE 'E%'
OR LastName LIKE 'F%'
OR LastName LIKE 'G%'
OR LastName LIKE 'H%'
OR LastName LIKE 'I%'
OR LastName LIKE 'J%'
OR LastName LIKE 'K%'


The previous query does find LastName values starting from A-K. However, if you need a range of letters, the LIKE operator has many better options. We only really care about the first letter of the last name and there a several first letters that fit with what were looking for. The first letter of the last name can be A,B,C,D,E,F,G,H,I,J or K. Simply list all the choices you want for the first letter inside a set of square brackets.

--LastNames ranging from A to K using a set of 11 letters
SELECT *
FROM Employee
WHERE LastName LIKE '[ABCDEFGHIJK]%'


Square brackets with wildcards enclose ranges or sets for 1 position. In this case the first position is a set of 11 different possible letters. This is not a series of letter but a multiple choice of letters. For example this works regardless of the order you put your letters in. This code sample below does the exact same thing.

--LastNames ranging from A to K using a set of 11 letters
SELECT *
FROM Employee
WHERE LastName LIKE '[KBCDEFGHIJA]%'


Again the set is how many letters you put in the square brackets. The code below is a logical mistake where you won’t get A to K but you just get A or K for the first letter.

--Find all LastNames starting with A or K (Mistake
SELECT *
FROM Employee
WHERE LastName LIKE '[AK]%'


Since we’re looking for the first letter to be within a range  from A to K, we specify that range in square brackets. This is even easier than using a set. The wildcard after the brackets allows any number of characters after the range.

--LastNames ranging from A to K using a range
SELECT *
FROM Employee
WHERE LastName LIKE '[A-K]%'


Note: this range will not work if your LIKE was changed to an equal (=) sign. The following code will not return any records to your result set:

--Bad query (it won’t error but returns no records)
SELECT *
FROM Employee
WHERE LastName = '[A-K]%'

Find Row Count in Table – Find Largest Table in Database

This script will gives row number for every table in database.

USE AdventureWorks
GO

 
----
SELECT OBJECT_NAME(OBJECT_ID) TableName, st.row_count
FROM sys.dm_db_partition_stats st
WHERE index_id < 2
ORDER BY st.row_count DESC
GO

---

select sc.name +'.'+ ta.name

,sum(pa.rows) -- Approximate value

from sys.tables ta

inner join sys.partitions pa

on pa.object_id = ta.object_id

inner join sys.schemas sc

on ta.schema_id = sc.schema_id

where ta.is_ms_shipped = 0 AND pa.index_id IN (1,0)

group by sc.name,ta.name

ORDER BY sum(pa.rows) DESC

GO


---- With Columns and Size

CREATE TABLE #temp (

table_name sysname ,

row_count INT,

reserved_size VARCHAR(50),

data_size VARCHAR(50),

index_size VARCHAR(50),

unused_size VARCHAR(50))

SET NOCOUNT ON

INSERT #temp

EXEC sp_msforeachtable 'sp_spaceused ''?'''

SELECT a.table_name,

a.row_count,

COUNT(*) AS col_count,

a.data_size

FROM #temp a

INNER JOIN information_schema.columns b

ON a.table_name collate database_default

= b.table_name collate database_default

GROUP BY a.table_name, a.row_count, a.data_size

ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC

DROP TABLE #temp