Posted by: rawbee May 15, 2008
Login in to Rate this Post:
0
?
Tips 1 list all database sizes in megabytes:
EXEC sp_MSforeachdb @command1="select '?', 0.0078125 * sum(size) from ?..sysfiles"
Tips 2 list all the primary keys, including composite primary keys, in the AdventureWorks database:
USE
AdventureWorks;SELECT
TableName
= o.name,PrimaryKey
= co.name,Rows
= i.rowsFROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS cINNER
JOIN sysindexes iON
c.constraint_name = i.nameand
CONSTRAINT_TYPE = 'PRIMARY KEY'INNER
JOIN sysindexkeys kON
i.id = k.idAND
i.indid = k.indidINNER
JOIN sys.columns coON
i.id = co.object_id ANDk
.colid = co.column_idINNER
JOIN sys.objects oON
co.object_id = o.object_idWHERE
o.type = 'U'AND
i.rows > 0order
by TableName, PrimaryKeyTips 3 How to find the nth highest price by color?
use
AdventureWorksselect
Color, ListPricefrom
(
select Color,ListPrice
,SequenceNo
=row_number() over (partition by Color order by ListPrice desc)from
Production.Productwhere
ListPrice > 0)awhere
SequenceNo=5
will be continue........