Posts Tagged ‘mssql’

Quick Tip: How to Fix “Invalid Object Name” for New Tables in SQL Server Management Studio (SSMS)

This big headache has a simple solution:

Edit -> IntelliSense -> Refresh Local Cache

SQL: Compare Columns of 2 Tables

SELECT * FROM (
SELECT * FROM Jason.INFORMATION_SCHEMA .COLUMNS WHERE TABLE_NAME='cool_table'
) a FULL OUTER JOIN (
SELECT * FROM [SOMESERVER].[SOMEDB] .INFORMATION_SCHEMA. COLUMNS WHERE TABLE_NAME='DIM_Product'
) b ON a.COLUMN_NAME =b. COLUMN_NAME
WHERE a .COLUMN_NAME IS NULL OR b.COLUMN_NAME IS NULL
OR a .DATA_TYPE<> b.DATA_TYPE OR a.IS_NULLABLE <>b. IS_NULLABLE
OR a .CHARACTER_MAXIMUM_LENGTH<> b.CHARACTER_MAXIMUM_LENGTH
OR a .NUMERIC_PRECISION<> b.NUMERIC_PRECISION OR a.NUMERIC_SCALE <>b. NUMERIC_SCALE
OR a .COLLATION_NAME<> b.COLLATION_NAME -- and maybe some other columns

Quick Tip: Create Table as Select (CTaS) Hack in MS SQL Server in 1 Line

select * into target_table from [DB]. [dbo].[table]

Quick Tip: Get the Size of MS SQL Server Table in 1 Line

sp_spaceused ‘[dbo].[table]’

MS SQL Server: Get Size of All Tables in a DB

SELECT 
    s.Name AS SchemaName,
    t.NAME AS TableName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM 
    sys.tables t
INNER JOIN 
    sys.schemas s ON s.schema_id = t.schema_id
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.RowsORDER BY 
    s.Name, t.Name

Quick Tip: Check Last Update Time of a SQL Table (Note: requires permission)

SELECT OBJECT_NAME (OBJECT_ID) AS DatabaseName, last_user_update,*
FROM sys .dm_db_index_usage_stats
WHERE database_id = DB_ID( ‘TomasTest’)
AND OBJECT_ID =OBJECT_ID( ‘dbo.forecast’)

MySQL v. MS SQL Server Information Schema Queries

In MySQL, you would run the following to get information on tables in a database:
SELECT * FROM information_schema.tables
WHERE table_schema = ‘YourDatabaseName’;
In MS SQL Server, the same would be:

USE YourDatabaseName
SELECT * FROM INFORMATION_SCHEMA.tables;
– See more at: http://dbadiaries.com/querying-information_schema#sthash.aQp6qbaP.dpuf

Quick Tip: MS SQL TOP = MySQL Limit

 The MS SQL equivalent of MySQL’s LIMIT clause is TOP. Example:
SELECT TOP 10 * FROM stuff;
Will return the top ten rows, effectively doing the same thing as
SELECT * FROM stuff LIMIT 10;