Tag Archives: T-SQL

T-SQL Script to identify tables without Primary Key

When designing tables, It is a good practice of having one column that is unique and can be a primary key. You can include one of the below type column as Primary Key

– Add Auto Increment Column
– Identify the column which unique for all the rows

Make sure Primary keys should be as small as necessary. Prefer a numeric data type because numeric types are stored in a much more compact format than character formats. Most primary keys will be foreign keys in another table as well as used in multiple indexes. The smaller your key, the smaller the index, the less pages in the cache.

You can execute below script to identify the tables without Primary Key and add the Primary Key into tables as per the above suggestions..


Use <Database Name>

SELECT SCHEMA_NAME(schema_id) AS [Schema Name], name AS [Table Name]
FROM sys.tables
WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasPrimaryKey') = 0
Order by name
GO

Script to determine identity table and identity column

Script to determine the identity tables and columns in the databases.

USE <DBNAME>
--script to determine the table with the identity column
select name, OBJECTPROPERTY(id, 'TableHasIdentity') AS TableHasIdentityCol
from sysobjects
where xtype = 'U'

--script to determine the table and columne with the identity on
SELECT OBJECT_NAME(id) as TblName, name as ColName
FROM syscolumns
WHERE status = 0x80

--script to determine the table and columne with the identity on using ColumnProperty
SELECT OBJECT_NAME(id) as TblName, name as ColName
FROM syscolumns
WHERE COLUMNPROPERTY(id, name, 'IsIdentity') = 1

Script to find out the database file size, log file size on SQL Server 2000/2005/2008

Recently I encountered a situation where i have to find out the database file size on SQL Server 2000. Here is the script which you can use to run on SQL Server 2000. Below script will on SQL Server 2005 or SQL Server 2008 as well.

You can use sys.master_files instead of sysAltfiles in SQL Server 2005 and SQL Server 2008. Please find all the different scripts below.

--SQL Server 2000

select 
fileID 
,name
,filename
,(size*8)/1024 SizeMB
,((size*8)/1024)/1024 SizeGB
,db_name(dbid) as DBName
from sysaltfiles
where db_name(dbid) = 'master'

--SQL Server 2000

select 
fileID 
,name
,filename
,(size*8)/1024 SizeMB
,((size*8)/1024)/1024 SizeGB
,db_name(dbid) as DBName
from sysaltfiles
where filename like '%.ldf%'

--SQL Server 2005/2008
SELECT DB_NAME(database_id) AS DBName,
Name AS LogicalName,
Physical_Name, (size*8)/1024 SizeMB
Physical_Name, ((size*8)/1024)/1024 SizeGB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'Master'
GO

How to check Lock Pages In Memory is enabled?

You can use below simple technique to check whether lock pages in memory is enabled or not. If lock pages in memory is enabled you can see the “Using locked pages for buffer pool” message in the SQL Server error log.

exec xp_readerrorlog 0, 1, 'locked pages' 

To check if it is disabled. You have to check for the “Address Windowing Extensions (AWE) requires the ‘lock pages in memory’ privilege which is not currently present in the access token of the process.” message.

exec xp_readerrorlog 0, 1, 'lock pages in memory' 

How much memory is used by each database in SQL Server?

A large percentage of the memory of SQL Server instance is consumed by buffer pool. You can find out the database which consuming more memory using below query.

SELECT
    CASE WHEN database_id = 32767 THEN 'Resource DB' ELSE DB_NAME (database_id) END AS 'DBName',
    COUNT (1) AS 'Page Count',
    (COUNT (1) * 8)/1024 AS 'Memory Used in MB' ,   
   CASE WHEN is_modified = 1 THEN 'Dirty Page' ELSE 'Clean Page' END AS 'Page State' 
FROM sys.dm_os_buffer_descriptors
   GROUP BY [database_id], [is_modified]
   ORDER BY   db_name(database_id)
GO