table information

How easy to determine table dependencies ? TIP # 86

  Determine the table dependencies is challenging sometime but we can easily resolve this by using a simple stored procedure which  SQL Server provides. By using this stored procedure we can easily determine all the dependencies of particular table. The stored procedure is sp_msdependencies We can use this stored procedure as shown below Execute sp_msdependencies …

How to determine Meta data or result set information of a stored procedure /trigger ? TIP # 73

  Sometimes it may require that you don’t know what will be output of  a stored procedure ? what kind of result set it return ? In such case SQL SERVER provided a new DMV statement which is sys.dm_exec_describe_first_result_set_for_object. In other words if we want to know result set’s meta data then we can use …

How to copy table structure only from a SQL Query ? Tip #69

  Recently , one of my friends shared that some interviewer asked him a question “How to copy table structure only from a  SQL Query?” So, Below is simplest query to copy structure only of a table into another table. SELECT * Into #tmpStudentStructureFROM tblStudentSource WHERE 1= 0 in the above query we want to …

What is Heap table in SQL Server and How to get all the heap table from a database ? TIP # 54

  Heap table:- A table without cluster index is called Heap table. Now you are thinking why we are talking about this. So as  SQL developer we should aware and In next post I will explain which is related to maintenance how to Rebuild a heap table. Now our next question is how to determine …

How to update statistics ? TIP #14

b In last TIP tip#13, We learn how to find last updated statistics status. Now we know when it last updated so it may be require we need to update statistics for some of table. So to update statistics we need to write following command ( if we want to update  statistics of entire tables …

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

To get file (MDF/LDF) information we can write following query GOEXEC sp_MSforeachdb ‘USE ?  Execute sp_helpFile’ GO Or we can write following commandGoEXEC sp_MSforeachdb ‘USE ? SELECT ”?”, SF.filename, SF.size FROM sys.sysfiles SF’Go Or we can write following commandGO SELECT Name,Physical_name As current_file_LocationFrom sys.master_Files Go 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 columnTypeFROM sys.columns scINNER JOIN sys.tables st ON st.object_id  = sc.object_idINNER JOIN systypes sty on sty.xtype = sc.system_type_id ORDER By st.name,sc.nameGO Or we can use following statement SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,IS_NULLABLE FROM …

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 GOSELECT * FROM sys.objects WHERE type=’U’ AND Name <> ‘sysdiagrams’GO or we can use following commandGOSELECT * FROM sys.tablesGO The result of above statement is same Or we can use one more command which is as follow SELECT * FROM INFORMATION_SCHEMA.TABLES …