Post
Ask Question

SQL Queries for Database Analysis

I shared few SQL queries useful in analyzing database, which I use quite often. This query will return all table names and no.of rows in it for built-in tables.
  1. -- List all table names and number of rows in it for user-defined tables  
  2. SELECT distinct t.name,prt.rows  
  3. FROM sys.tables t INNER JOIN sys.partitions AS prt  
  4. ON t.object_id = prt.object_id where t.is_ms_shipped=1 -- 0 for user-defined tables  
  5. order by prt.rows desc  
This query will return column names and its data type of a table.
  1. -- Get column names and its types of a table  
  2. SELECT cols.name,t.name  
  3. FROM sys.objects o join sys.columns cols on o.object_id= cols.object_id  
  4. join sys.types t on t.system_type_id=cols.system_type_id  
  5. and o.name='Employee'-- Table Name 
This query will return file name, its size and file group name of a database.
  1. SELECT sdf.name AS [FileName],  
  2. size/128 AS [Size],  
  3. fg.name AS [File_Group_Name]  
  4. FROM sys.database_files sdf  
  5. INNER JOIN  
  6. sys.filegroups fg  
  7. ON sdf.data_space_id=fg.data_space_id  
Batch file to execute all sql files in a directory, Save it as .bat in a folder that have sql script files to be executed.
  1. @Echo Off  
  2. FOR /f %%i IN ('DIR *.Sql /B') do call :RunSql %%i  
  3. GOTO :END  
  4. :RunSql  
  5. Echo Executing SQL: %1  
  6. SQLCMD -S server1 -U user1 -P pwd1 -d DB1 -i %1  
  7. Echo Completed SQL: %1  
  8. :END  
This query will return all table names that have a Foreign key:
  1. SELECT SCHEMA_NAME(schema_id) AS SchemaName,  
  2. name AS TableName  
  3. FROM sys.tables where OBJECTPROPERTY(OBJECT_ID,'TableHasForeignKey') = 1 -- Return all tables having Foreign key