Tag Archives: Database Objects

Alter an existing object rather than Drop and Create

Posted on by
2

In this blog I would like to write on a topic that I see many developers (and few DBA’s too) doing without trying to understand or think about the complete picture of what is affected. The task of making changes to an existing database object such as tables, stored procedures,  views etc.

Many times, database objects will have to be modified (altered) at a later time, for a table or view it would be adding new columns or changing the data types of the existing columns. For stored procedures it would be change in logic that needs to be incorporated. There are multiple ways to achieve this, one way is to drop the existing one and create the newer one (most probably for a view or stored procedure), the other way is to alter the existing one. What a user needs to keep in mind is, that when you drop a database object and create the new one, the underlying security permissions get whacked and those users / logins that have been assigned additional permissions to these objects will not have the permissions to carry out their task, they should be reassigned with those permissions on the newly created database.

Hence I feel, it is better to alter the existing one instead of dropping and creating.

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

– Bru Medishetty

How to find when was a database object created in SQL Server?

Posted on by
Reply

In this blog we shall learn simple task, How to find the date and time when a database object was created. This is possible in multiple ways and we shall see all of them….

From Object Explorer Details

This is the easiest way of all… When connected to the database Instance using SSMS, in the Object Explorer (left side pane of SSMS), expand the database node and click on tables node. In the object explorer details, you can see the list of tables or other database objects when clicked on those nodes. One of the default columns displayed is the Create Date. This was you can sort the database objects by object names and find the create date of the object.

Status Bar in Object Explorer Details

The next way to find the same information is to expand the appropriate node (tables or Views or Programmability for stored procedures, functions etc..). Once you select the database object, you can find the details about the object in the bottom portion of the object explorer details (right hand pane in SSMS). In that status bar you can find the create date of that object (as shown below)..

Object Properties

Another method most people use is by right clicking on the database object and choosing properties from the pop-up menu items. That causes the properties dialog box for that object and you can find the create date information under the group description (as shown below)

Querying the System Catalog Views

Another way to find the create date along with time is using System Catalog Views. You can find it by querying sys.objects by providing a filter condition based on the object name and type. You might need to specify more details with respect to what kind of database object you are trying to find. For tables use type_desc as ‘USER_TABLE’ and for Store d Procedures use ‘SQL_STORED_PROCEDURE’. Doing this will result in narrowing down and display the distinct record that you are trying to find. 

You can also query the system catalog view for the database objects such as sys.procedures, sys.tables, sys.views or sys.triggers etc…

These catalog views contains a wide number of columns, and for this example I chose only two columns, the name of the database object and the create_date column..

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

— Bru Medishetty