Friday, November 30, 2012

SQL Azure Statements

I got a project to work on Azure database stuff. I will document the technical pain point(s) for this project and hopefully it help somebody and also as my own reference. (I am human, I do forget. ;-) )

  • create database
    CREATE DATABASE MyDatabase (Edition='web')

  • delete database
    DROP DATABASE MyDatabase

  • alter database name
    ALTER DATABASE MyDatabase MODIFY NAME=NewName

  • create index
    CREATE CLUSTERED INDEX index0
    ON TestTable ( < your column name >  )

  • get used space
    SELECT sum(reserved_page_count) * 8.0 / 1024
    FROM sys.dm_db_partition_stats

  • Check if the table is in database
     SELECT   
      CASE WHEN count(*) > 0 THEN 1 ELSE 0 END AS TableExist  
     FROM  
      sys.tables t  
     JOIN  
      sys.schemas s  
       ON t.schema_id = s.schema_id  
     WHERE  
      s.name = 'dbo' AND t.name = 'TestTable'  
    

    Another version using INFORMATION_SCHEMA
     SELECT   
      CASE WHEN count(*) > 0 THEN 1 ELSE 0 END   
      AS TableExists  
     FROM INFORMATION_SCHEMA.TABLES   
     WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'TestTable'  
    

No comments: