How to determine table reference /used in stored procedure- TIP #9

To determine table used in stored procedure or table reference

We need to run following command
Go
SELECT object_Name(Id)as objectName,text FROM syscomments  WHERE text like '%tblUser%'
Go





We can use following command as well
Go
SELECT ROUTINE_NAME,ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION like '%tblUser%'
 GO


In the above queries we have used tblUser as table name we can replace it and add any other table name which we want to find.
Enjoy!!!

How to determine foreign key of a database - TIP #8

To determine foreign key of the database we need to run following command

Go
SELECT NAME,
OBJECT_NAME(Parent_Object_Id) As PrimaryTable,
object_Name(Referenced_Object_Id) As ReferenceTable,
create_Date,
Modify_Date
FROM sys.Foreign_Keys
WHERE type ='F'
GO



Enjoy!!!

How to determine current SQL Version - TIP #7

To determine SQL Server version we need to run following command
Go
@@Version
GO




Enjoy !!!

Get files (MDF/LDF) information of all databases – TIP #6

To get file (MDF/LDF) information we can write following query

GO
EXEC sp_MSforeachdb 'USE ?  Execute sp_helpFile'

GO


Or we can write following command
Go
EXEC sp_MSforeachdb 'USE ? SELECT ''?'', SF.filename, SF.size FROM sys.sysfiles SF'
Go


Or we can write following command
GO

SELECT Name,Physical_name As current_file_Location
From sys.master_Files

Go


Enjoy !!!

Get all stored procedures name from Database -Tip #5

To get all stored procedures from database then we can use following command
GO
SELECT name,type_Desc,create_Date,Modify_Date
FROM sys.procedures WHERE type='p'
AND type_desc ='SQL_STORED_PROCEDURE'
GO



Or we can use following command
GO
SELECT ROUTINE_NAME,ROUTINE_TYPE,ROUTINE_DEFINITION,CREATED,LAST_ALTERED
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE ='PROCEDURE'




Enjoy  !!!

GET all columns of a table–TIP #4

 

To Get all columns information of a table we can use following command

GO

SELECT st.name, sc.name,sc.is_identity AS IdentityColumn,
sc.is_nullable AS NullableColumn,
sc.max_length as colmaxlength,
sty.name as columnType
FROM sys.columns sc
INNER JOIN sys.tables st ON st.object_id  = sc.object_id
INNER JOIN systypes sty on sty.xtype = sc.system_type_id
ORDER By st.name,sc.name
GO

Or we can use following statement

SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
IS_NULLABLE
FROM [INFORMATION_SCHEMA].[COLUMNS]

Enjoy !!!

Get all the tables name from a database –TIP #3

We can get all the table’s name from a database using following simple syntax

GO
SELECT * FROM sys.objects WHERE type='U' AND Name <> 'sysdiagrams'
GO



or we can use following command
GO
SELECT * FROM sys.tables
GO
The result of above statement is same

Or we can use one more command which is as follow

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME <>'sysdiagrams'
Go



For more information you can visit http://msdn.microsoft.com/en-us/library/ms190324.aspx

Enjoy !!!

sp_help a way to know detail tip #2

To determine table detail like column name & data types for each individual table ,Index, primary key, foreign keys etc,  sp_help is one of the best way.

Syntax of sp_help is very simple as shown below

sp_help tableName

GO

Instead of writing  sp_help tablename you can get same information by highlighting table and press ALT+ F1

Note: if you just right sp_help and run it then all the objects of database will be listed down as a result.

Enjoy !!!

SP_SPACEUSED–Determine size (Space) - SQL Server TIP #1

 

To determine size of table in a database or size of database, we have simple syntax in SQL Server which is SP_SPACEUSED.

Syntax is simple enough as shown below

1) If we want to determine size of the database then we have to write below statement and execute

Use DatabaseName

GO

SP_SPACEUSED

GO

2) Now to determine size of particular table  we need to write following syntax

GO

SP_SPACEUSED TABLENAME

GO

3) Now to determine size of each table in a database we need to write following syntax

GO

SP_MSFOREACHTABLE ‘SP_SPACEUSED [1]’

GO

4) When we are running sp_spaceused it may possible the size is not updated so to make sure size is updated we need to write following command

GO

SP_SPACEUSED @updateUsage =N’True’

GO

In the output of each statements we are seeing reserved, data,Index_Size & unused columns.

so

1) reserved column shows total space reserved by object.

2) data column shows total space used by the data.

3) index_Size shows total space used by index in object.

4)unused shows total space reserved by object but not yet used.

For more information you can visit following link

http://msdn.microsoft.com/en-us/library/ms188776.aspx

Enjoy.