Category Archives: Notes

All Articles

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

The CHECK_POLICY and CHECK_EXPIRATION options cannot be turned OFF when MUST_CHANGE is ON. (Microsoft SQL Server, Error: 15128)

Leave a reply

Problem: Recently one of my user reported the issue that, He is getting password change message while login to SQL Server. But he does not want to change the password as he has already configured at the many places. While turning off the Password Expiration message, I got the below error.

The CHECK_POLICY and CHECK_EXPIRATION options cannot be turned OFF when MUST_CHANGE is ON. (Microsoft SQL Server, Error: 15128)

Solution:
To fix the issue you MUST have to change the password first without changing in password policy options. The good thing it that you can change the password to be the same as the existing one. -:)

But still you have to change the password -:) what a logic!!! You can do it from SSMS GUI as well, but I always prefer to use the scripts as it is giving me flexibility of customization.

That was the another reason I am in the support of open source software you have flexibility to add your ideas. Let’s go to fix the issue.

In the below step I am giving the same password, to fix “User Must Change Password” flag.

USE Master
GO
ALTER LOGIN [Login Name] WITH PASSWORD = 'Same Password'
GO

Now let’s turn off the passowrd policy.

ALTER LOGIN [Login Name] WITH
      CHECK_POLICY = OFF,
      CHECK_EXPIRATION = OFF;

Script to determine identity table and identity column

5 Replies

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