August 2, 2019 12:03 by
Peter
In this post, I'll share few useful SQL queries for database analysis on SQL Server 2012. 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.
-- List all table names and number of rows in it for user-defined tables
SELECT distinct t.name,prt.rows
FROM sys.tables t INNER JOIN sys.partitions AS prt
ON t.object_id = prt.object_id where t.is_ms_shipped=1 -- 0 for user-defined tables
order by prt.rows desc
This query will return column names and its data type of a table.
-- Get column names and its types of a table
SELECT cols.name,t.name
FROM sys.objects o join sys.columns cols on o.object_id= cols.object_id
join sys.types t on t.system_type_id=cols.system_type_id
and o.name='Employee'-- Table Name
This query will return file name, its size and file group name of a database.
SELECT sdf.name AS [FileName],
size/128 AS [Size],
fg.name AS [File_Group_Name]
FROM sys.database_files sdf
INNER JOIN
sys.filegroups fg
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.
@Echo Off
FOR /f %%i IN ('DIR *.Sql /B') do call :RunSql %%i
GOTO :END
:RunSql
Echo Executing SQL: %1
SQLCMD -S server1 -U user1 -P pwd1 -d DB1 -i %1
Echo Completed SQL: %1
:END
This query will return all table names that have a Foreign key:
SELECT SCHEMA_NAME(schema_id) AS SchemaName,
name AS TableName
FROM sys.tables where OBJECTPROPERTY(OBJECT_ID,'TableHasForeignKey') = 1 -- Return all
HostForLIFE.eu SQL Server 2012 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.