This post is second part of my previous post about SQL SERVER – 2005 – List All Stored Procedure Modified in Last N Days
SQL SERVER – 2005 – List All The Column With Specific Data Types
Since we upgraded to SQL Server 2005 from SQL Server 2000, we have used following script to find out columns with specific datatypes many times. It is very handy small script. SQL Server 2005 has new datatype of VARCHAR(MAX), we decided to change all our TEXT datatype columns to VARCHAR(MAX).…
Read MoreSQL SERVER – 2005 – List Tables in Database Without Primary Key
This is very simple but effective script. It list all the table without primary keys. USE DatabaseName; GO SELECT SCHEMA_NAME(schema_id) AS SchemaName,name AS TableName FROM sys.tables WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasPrimaryKey') = 0 ORDER BY SchemaName, TableName; GO Reference : Pinal Dave (https://blog.sqlauthority.com), BOL
Read MoreSQL SERVER – 2005 List All Tables of Database
This is very simple and can be achieved using system table sys.tables. USE YourDBName GO SELECT * FROM sys.Tables GO This will return all the tables in the database which user have created. Reference : Pinal Dave (https://blog.sqlauthority.com) , BOL
Read MoreSQL SERVER – Fix : Error 2501 : Cannot find a table or object with the name . Check the system catalog.
Error 2501 : Cannot find a table or object with the name . Check the system catalog. This is very generic error beginner DBAs or Developers faces. The solution is very simple and easy. Follow the direction below in order. Fix/Workaround/Solution: Make sure that correct Database is selected. If not…
Read MoreSQL SERVER – Query to Find Seed Values, Increment Values and Current Identity Column value of the table
Following script will return all the tables which has identity column. It will also return the Seed Values, Increment Values and Current Identity Column value of the table. SELECT IDENT_SEED(TABLE_NAME) AS Seed, IDENT_INCR(TABLE_NAME) AS Increment, IDENT_CURRENT(TABLE_NAME) AS Current_Identity, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'TableHasIdentity') = 1 AND TABLE_TYPE = 'BASE…
Read More