SQL System Table Archives - Page 13 of 13 - SQL Authority with Pinal Dave

SQL SERVER – 2005 – List All The Column With Specific Data Types

August 9, 2007

SQL Datatype, SQL Scripts, SQL System Table

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).…

SQL SERVER – 2005 – List Tables in Database Without Primary Key

August 7, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

Database, SQL Constraint and Keys, SQL Scripts, SQL System Table

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

SQL SERVER – 2005 List All Tables of Database

June 26, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Scripts, SQL System Table

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

SQL SERVER – Fix : Error 2501 : Cannot find a table or object with the name . Check the system catalog.

May 9, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Error Messages, SQL Scripts, SQL System Table

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…

SQL SERVER – Query to Find Seed Values, Increment Values and Current Identity Column value of the table

April 23, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Function, SQL Identity, SQL Index, SQL Scripts, SQL Server, SQL System 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…