Category Archives: SQL Server 2008

T-SQL Script to identify tables without Primary Key

Leave a reply

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

T-SQL Script to identify the data, log and backup drive

Leave a reply

You can use the below script to identify the data, log and backup file drive.

select 
@@SERVERNAME as svrName,
drivename, 
drivedescription
from
(
select distinct  SUBSTRING(filename,1,3) as drivename, 'data Drive' as drivedescription from master..sysaltfiles where filename like '%.mdf'
union
select distinct  SUBSTRING(filename,1,3) as drivename, 'Log Drive' as drivedescription from master..sysaltfiles where filename like '%.ldf'
union 
select distinct  SUBSTRING(physical_device_name,1,3) as drivename, 'Backup Drive' as drivedescription from msdb.dbo.backupmediafamily where physical_device_name like '%.bak'
) tab1

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

Leave a reply

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

Saving changes is not permitted while modifying table design

1 Reply

Problem: There may be scenario while modifying the table design which needs to re-create the table. Even with the full permission while modifying it from SSMS, you get the below message and your table changes will be rolled back.

Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created.

Solution:
Below are the few scenarios where changes in table design will re-create the table.

Changing the column data type
Changing the NULL property
Changing the order of the columns
Adding column before the last column
Changing the identity column property
Changing the computed column expression

Let’s see how can we fix the “Saving Changes is not permitted …”

Go to SSMS – Select Tools menu -> click on Options -> Go to Designers tab page -> Click on “table and database designers” page -> Un-check “Prevent Saving changes that require table re-creation” check box.