The following script can be use to get the list of all Primary Key, Foreign Key and Unique Key. The following script can be used to get the list of all Indexes. Thanks Manish
Month: October 2012
Data Dictionary
The following script is used to create Data Dictionary for all tables. However these above script can be modified to get details of any specific schema. Thanks Manish
Stored procedures(SP) Vs Functions(UDF)
Stored Procedures Functions Use Used to read and modify data. Used only to read data Execution Execute or Exec is used to execute SP Can used with SELECT statement, JOINS & APPLY (CROSS & OUTER). Joins Cannot JOIN a SP in a SELECT statement. Can JOIN a UDF in a SELECT statement. Temp objects Can … Continue reading Stored procedures(SP) Vs Functions(UDF)
Check Constraint in existing data.
Few days back I was trying to add a check constraint in a table, but the condition was such like that I do not have to worry about existing data, what I need to do was to restrict the new data with the valid constraints. Let's create a table and insert few records. Add a … Continue reading Check Constraint in existing data.
Results as comma separated
Many times we needed to have some values as comma separated , the following are few scenarios. Single Table Following shall be output Having two table and needed joins Following shall be output The following is other way of doing without STUFF function and with Substring. Data selected from a single table (Table2) … Continue reading Results as comma separated
Delete duplicate records
The following are the ways to delete duplicate rows from a table, and its purely depends upon your environment, its recommend to first try it on development server and then move ahead to production environment. Using group by clause and move the records to another table, truncate original and move records back to original. There … Continue reading Delete duplicate records
Find number of tables used in stored procedure.
While doing analysis or reviewing database objects often we used to look and think for objects which are being used in any specific stored procedure or stored procedures. Following is the small script which is used to find out all the table used in stored procedures. -- Query to get list of all table used … Continue reading Find number of tables used in stored procedure.
Count number of rows in stored procedure
Following code is used to count number of rows in stored procedure.
Performance tuning questions in SQL Server.
What are the various tools you used for Sql Server Query optimization/Performance tuning Explain how do you optimize Server for better performance Explain how do you optimize Database for better performance ( Explain in details about, what all we can do with TempDB for better performance) Explain how do you optimize Query for better performance … Continue reading Performance tuning questions in SQL Server.
SQL Diary & my notes 