A few days ago, I was invited to one of the largest database company. I was asked to review database schema and propose changes to it. There was special username or user logic was created for me, so I can review their database. I was very much interested to know what kind of permissions I was assigned per server level and database level. I did not feel like asking their Sr. DBA the question about permissions.
SQL SERVER – Difference Between @@Version and xp_msver – Retrieve SQL Server Information
Just a day ago, I was asked which SQL Server version I am using. I said SQL Server 2005. However, the person I was talking was looking for more information then that. He requested more detail about the version. I responded with SQL Server 2005 Service Pack 2. After the…
Read MoreSQL SERVER – 2005 – List All The Constraint of Database – Find Primary Key and Foreign Key Constraint in Database
Following script are very useful to know all the constraint in the database. I use this many times to check the foreign key and primary key constraint in database. This is simple but useful script from my personal archive. USE AdventureWorks; GO SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint, SCHEMA_NAME(schema_id) AS SchemaName, OBJECT_NAME(parent_object_id)…
Read MoreSQL SERVER – 2005 – Find Tables With Primary Key Constraint in Database
My article SQL SERVER – 2005 Find Table without Clustered Index – Find Table with no Primary Key has received following question many times. I have deleted similar questions and kept only latest comment there. In SQL Server 2005 How to Find Tables With Primary Key Constraint in Database? Script…
Read More
SQL SERVER – 2005 – Find Tables With Foreign Key Constraint in Database
While writing article based on my SQL SERVER – 2005 Find Table without Clustered Index – Find Table with no Primary Key I got an idea about writing this article. I was thinking if you can find primary key for any table in the database, you can sure find foreign key for any table in the database as well.
SQL SERVER – 2005 – Search Stored Procedure Code – Search Stored Procedure Text
I receive following question many times by my team members. How can I find if particular table is being used in the stored procedure? How to search in stored procedures? How can I do dependency check for objects in stored procedure without using sp_depends? I have previously wrote article about…
Read MoreSQL SERVER – 2005 – Find Database Status Using sys.databases or DATABASEPROPERTYEX
While writing article about database collation, I came across sys.databases and DATABASEPROPERTYEX. It was very interesting to me that this two can tell user so much about database properties. Following are main database status: (Reference: BOL Database Status) ONLINE Database is available for access. OFFLINE Database is unavailable. RESTORING One…
Read More