See Object Explorer Details for objects in this folder |

MS SQL Server Management Studio missing databases |

MS SQL 2008 Database missing tables|

Connect to MS SQL 2008 Server missing databases and tables

Several days ago, I was trying to connect to Microsoft SQL Server 2008 on my hosting account. It connects successfully but I don't see any database when expanding the Databases folder. There is an unusual blue icon under the folder with the label "See Object Explorer Details for objects in this folder". Then I compared the build version of Microsoft SQL Management Studio on my machine and the SQL server 2008 version on the hosting account and I noticed that the version I have is not up-to-date. Everything are working correctly again after installing all the necessary service pack and critical update. If you come across this situation, first check the version of SQL Management Studio. Here is the step to check the versions. Then download/install the latest service pack or update from Microsoft website or run the windows update. I also found this link useful, it provides all the KB / Descriptions for all build.

Figure 1

Problem:

You are getting the error message shown below while trying to restore a database.

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
ADDITIONAL INFORMATION:
The media family on device 'C:\ysatech\db\MyDB.bak' is incorrectly formed.

SQL Server cannot process this media family.
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3241)
For help, click: here


Solution:

Make sure that you are not restoring the backup from SQL 2008 to SQL 2005 instance.

Could not find stored procedure 'sp_help_jobhistory'.

sp_help_jobhistory stored procedure provides information about the cause of a job failure. If we try

EXEC sp_help_jobhistory

we will see the below error

Msg 2812, Level 16, State 62, Line 1 Could not find stored procedure 'sp_help_jobhistory'.

because the sp is resided in the MSDB database.
The solution is: EXEC msdb..sp_help_jobhistory

List Columns And Data Types In Sql Server

How to list my SQL table column/field names?

List columns and attributes for every table in a SQL Server database

List down the DataType and Size Of all Columns of All Tables in a SQL Server 2005/2008 Database

A couple days ago, my co-worker had asked if I could provide a list of all the columns in certain tables in some of our databases to share with a vendor. This vendor needed to know the column names, data type and max length, of each of the columns in the database. If you happen to come across this kind of situation, here is a stored procedure that will help you list the data type and size of all the columns of the selected or all the tables in a SQL Server 2005/2008 Database.

Here is the link to download the function UTILfn_Split

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        BT
-- Create date: 08312009
-- Description:   list table name, column name, data type…
/*
Util_ListDBSchema N'MyDatabase', N'tbl_Class,MyTempTable1' –several tables
Util_ListDBSchema N'MyDatabase', N'*' –-all tables
Util_ListDBSchema N'MyDatabase', N'tbl_movies' –one table
*/
-- =============================================
CREATE PROCEDURE dbo.Util_ListDBSchema
(
      @dbName varchar(100), 
      @tableName varchar(500)
)
AS
BEGIN
      SET NOCOUNT ON;
      DECLARE @strSql varchar(2500), @strWhere varchar(1000)
      
--list tables only
      SET @strWhere = 'objectproperty(object_id(table_name),''IsTable'') = 1'  
      
      IF (@tableName <> '*')
            SET @strWhere = @strWhere + 'and
      table_name in (SELECT * from dbo.UTILfn_Split ('''+@tableName+''','',''))'
      
      SET  @strSQL = N'
      USE ['+ @dbName +']
      
	  SELECT
		table_catalog, table_schema, table_name, column_name, 
		data_type, character_maximum_length as max_length
      FROM information_schema.columns 
      WHERE '+@strWhere+' ORDER BY table_name, column_name
'
 
-- print @strSQL
 exec (@strSQL)
 
END
GO