Monday, December 17, 2012

How to get list of sql server database objects?

Problem: How to get list of database objects?

 Solution: You can get list of objects in two ways.

1. Getting the list from sys.objects table. Ex:
 
List of tables:

SELECT *
FROM sys.objects where type='U'

List of stored procedures:

SELECT *
FROM sys.objects where type='P'

List of views:

SELECT *
FROM sys.objects where type='V'


2. Getting the list query from object related tables. Ex:


List of tables:


SELECT *
FROM sys.Tables


List of stored procedures:


SELECT *
FROM sys.procedures


List of views:


SELECT *
FROM sys.views




















How to remove all data from table and reseed identity column to initial value?

Problem: How to remove all data from table and reseed identity column to initial value?

 Solution: For this purpose best solution is to use truncate sql command when you want to delete all data and reset identity column value to initial value.
Ex. TRUNCATE TABLE tableName

Somebody uses the following command which results same output:

DELETE FROM tableName
DBCC CHECKIDENT (tableName,RESEED, 0)