MDS Client version incompatible with Database version

I    I faced an issue when we upgrade our database engine from RTM to SP1, I was getting the below shown message.

Resolution

Goto the MDS Configuration Manager – Select Database – Choose appropriate DB –Then Click on Upgrade Database

SQL Server Reporting Services - Error 1053

Issue : SSRS found stopped and i have got the below error while trying the restore.
---------------------------
Services
---------------------------
Windows could not start the SQL Server Reporting Services (INST1) service on Local Computer.
Error 1053: The service did not respond to the start or control request in a timely fashion.
---------------------------
OK  
---------------------------

Resolution

1. Click Start, click Run, type regedit, and then click OK.

2. Locate and then click the following registry subkey:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control

3. In the right pane, locate the ServicesPipeTimeout entry.

Note If the ServicesPipeTimeout entry does not exist, you must create it. To do this, follow these steps:

a. On the Edit menu, point to New, and then click DWORD Value.

b. Type ServicesPipeTimeout, and then press ENTER.

4. Right-click ServicesPipeTimeout, and then click Modify.

5. Click Decimal, type 60000, and then click OK.
    This value represents the time in milliseconds before a service times out.

6. Restart the computer.

Linked Server Error from SQL to ORACLE

The below error is reported by our application team, when they tried to access records from Oracle database using SQL server linked server. I came to know that this was working fine till the last day without issues.

Resolution

1) Checked the Provider properties of OraOLEDB.Oracle and the AllowInprocess 
    was checked.
2) I had to restart (fail over) the instance to make it functional.

SQL Cluster Failure

Yesterday one of our developer informed me that they are not able to connect to SQL Server. When i checked it in cluster manager, the services found failed and i could see the below error in event log.

  

Reason: SQL Service account password got changed and its not updated against the service.

Solution: Updated the password against the services and brought the resource online.

3203 - Restore Error SQL Server 2008

One of my friend got the below error while restoring a DB. When we verify the file, it gave us valid file, but the restore was not working using GUI. 

Resolution
Restore the DB with MOVE command using TSQL.

Assign explicit permission to User Defined SPs

/*------------------------------------------------------------------------------
Assign explicit permission to User defined SPs
1) Correct the DB name in USE statement before the execution.
2) Assign the correct user to the @USER.
-------------------------------------------------------------------------------*/
SET NOCOUNT ON
USE TEST -- CHANGE THE DB HERE
DECLARE @USER VARCHAR(50)
SET @USER='TEST2'----CHANGE THE USER HERE
DECLARE  @SECUREPERMISSION TABLE
(
ID INT IDENTITY(1,1),
OBJNAME VARCHAR(100),
SCHNAME VARCHAR(100)
)

INSERT INTO @SECUREPERMISSION
SELECT O.NAME,S.NAME FROM SYS.OBJECTS O
INNER JOIN SYS.SCHEMAS S ON O.SCHEMA_ID=S.SCHEMA_ID
WHERE TYPE='P'

DECLARE @COUNT INT, @PROCESSCOUNT INT, @CMD VARCHAR(200)
SET @COUNT=1
SELECT @PROCESSCOUNT= COUNT(ID) FROM @SECUREPERMISSION
WHILE @COUNT<=@PROCESSCOUNT
BEGIN
SET @CMD='GRANT EXECUTE ON'
SELECT @CMD= @CMD +' '+SCHNAME+'.'+ OBJNAME +'' FROM @SECUREPERMISSION WHERE ID=@COUNT
SET @CMD=@CMD+ ' TO ' +@USER
EXECUTE (@CMD)
--SELECT @CMD
SET @COUNT=@COUNT+1
END
SET NOCOUNT OFF

Report Server DNS Issue

I had an issue with my report server that the report manager URL is not working when i use it like below.
http://servername/reports
But it was working when i gave the IP address instead of hostname.
eg: http://192.168.10.1/reports
Resolution:
In order to overcome the issues with URLs and constant credential requests I changed the following settings in RSReportServer.config:

Original:
<Authentication>
      <AuthenticationTypes>
             <RSWindowsNegotiate />
             <RSWindowsNTLM />
      </AuthenticationTypes>


Adjusted to:
<Authentication>
      <AuthenticationTypes>
             <RSWindowsNTLM />
      </AuthenticationTypes>


Such changes were necessary due to the fact that there is no correct SPN set for the service account used by SSRS. If Kerberos authentication is required the correct SPN needs to be set up in prior.

Unable to begin a distributed transaction using linked server.

One of my client reported me that they are getting below given error when they were executing a query similar to below.


insert into <Table Nam> exec <LinkedServer Name>.<schema>.<DB Name>.<SP Name>


OLE DB provider "SQLNCLI" for linked server "LinkedServer Name" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Line 3
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "LinkedServer Name" was unable to begin a distributed transaction.

The below Setting Resolved the issue. I had to do the same in both servers.

1. open up "Component Services"

Control Panel > Administrative tools > Component Services

2. in Component Servies, right click "My Computer" and select "Properties"

Console Root > Component Services > Computers > My Computer

3. select the "MSDTC" tab (Select appropriate MSDTC if its clustered) , and click "Security Configuration" in the "Transaction Configuration" group box

4. Enable Allow inbound and Allow outbound.

5. in "Security Settings" > "Transaction Manager Communication"

select "No Authentication Required"

6. restart the DTC service (should do so automatically).

"Microsoft Cluster Service (MSCS) cluster verification errors" failed.

One of my friend received the below shown error during the add node installation of SQL Server 2008 on a Windows Server 2008 machine.

Solution: If we are sure that the cluster verification doesn't have any issue, we can complete the installation using the below commands.

For an integrated failover Add-Note setup, run the following command on each node that is being added:

Setup /SkipRules=Cluster_VerifyForErrors /Action=InstallFailoverCluster

For an advanced or enterprise installation, run the following command:

Setup /SkipRules=Cluster_VerifyForErrors /Action=CompleteFailoverCluster

If you receive this validation failure when you add a node to an existing failover installation, run the following command on each node that is being added: 

Setup /SkipRules=Cluster_VerifyForErrors /Action=AddNode

Table Diff to identify data mismatch.

1) Run the below query in Publisher.
2) Paste the output in Command Prompt.
3) See the output file for the mismatch.


Declare @sourceserver nvarchar(100) = '<Publisher Server>'
Declare @destinationserver nvarchar(100) = '<Subscriber Server>'

select '"C:\Program Files\Microsoft SQL Server\100\COM\tablediff.exe" -sourceserver [' + @sourceserver +
'] -sourcedatabase [BCP_Tran] -sourcetable [' + name +'] -sourceschema [' + SCHEMA_NAME(schema_id ) +
'] -sourcelocked [TABLOCK] -destinationserver [' + @destinationserver + '] -destinationdatabase [BCP_Tran] -destinationtable [' + name +
'] -destinationschema ['+ SCHEMA_NAME(schema_id )  +'] -destinationlocked [TABLOCK] -f c:\TableDiff_' + name+ '.sql'
      from  sys.tables 
      where type_desc = 'USER_TABLE' and is_published=1
      

SQL Server 2008 Cluster Collation Change

1. First make sure you have all the database backups including system            database.
2. Generate scripts for all the logins, Linked Servers, Server Triggers etc.
3. Generate scripts for all the Jobs exist on server.
4. Reset the SA password if you don’t know. And save it to safe place.
5. To change the collation for SQL Server 2008, If we are changing from same node, First we need to take resources offline (SQL Server, SQL Agent, Fulltextsearch) and run the below syntax on command prompt from SQL Server Setup directory.




C:\SQL>Setup.exe /QUIET /ACTION=REBUILDDATABASE /INSTANCENA
ME=INST1 /SQLSYSADMINACCOUNTS="Domain\Account" /SAPWD=<SA Password> /SQLCOLLATION=<New Collation>

Cross Domain Transactional Replication

We had the below error in my environment during cross domain pull transactional replication. To resolve this we have the below two options.

First Method

1) Generate the snapshot and manually copy the same to subscriber.

2) Change the Snapshot Location in the subscriber properties. Since the snapshot location is local, it will apply the same very fast.

Second Method

1) Keep the replication as it is.

2) Generate the BCP out of the articles.

     bcp BCP_Tran.dbo.Test out c:\Test.dat -c -U sa -P <Pword>-S <Srvr Name>

3) Apply the BCP file to the subscriber.

     bcp BCP_Tran.dbo.TestNew in c:\Test.dat -T -c -S <Server Name>

The SQL Server failover cluster instance name 'INST3' already exists as a clustered resource. Specify a different failover cluster instance name.

I got the below mentioned error while installing sql server 2008 failover cluster.


The SQL Server failover cluster instance name 'INST3' already exists as a clustered resource. Specify a different failover cluster instance name.


Resolution


1) Run Cluster Res in the command prompt. Get the orphaned resource from  
    the  list.
2) Delete the orphaned user using the below command.
        cluster RES "<Resource Name>" /Delete
3) Continue the installation after this.


         

SQL Server 2008 Cluster Add Node Installation Error - SKU is Invalid

I got the below error while adding node to the Second Instance of my SQL Server 2008 Cluster.

Solution

1) Open DefaultSetup file inside corresponding folder ( X64 for 64 bit processor, X86 for 32 bit).

2) Remove or Comment the PID line.

3) During the Installation give the product key manually.

SQL Server 2005 Cluster Error - Error: 422

One of my friend got the below error in our SQL Server 2005 environment.


000016bc.000009f4::2011/03/02-00:46:48.840 ERR  SQL Server <SQL Server (DWHCI01)>: [sqsrvres] StartResourceService: StartService (MSSQL$DB1) failed.  Error: 422
000016bc.000009f4::2011/03/02-00:46:48.840 ERR  SQL Server <SQL Server (DWHCI01)>: [sqsrvres] OnlineThread: ResUtilsStartResourceService failed (status 422)
000016bc.000009f4::2011/03/02-00:46:48.840 ERR  SQL Server <SQL Server (DWHCI01)>: [sqsrvres] OnlineThread: Error 422 bringing resource online
Application logs is showing similar errors.


Resolution
Found that SQL Server service is in Disabled status. Made the same to Manual and bring the resource online.

SQL Server Cluster Installation Error

I got the below shown error during SQL cluster installation. It can be resolved by doing the below command.

SETSPN -A MSSQLSVC/VirtualServerName.XX.XX.EDU:InstanceName <STARTUP ACCOUNT NAME>

Please refer the below URL for more information.

http://blogs.msdn.com/b/karthick_pk/archive/2009/03/27/installation-of-sqlserver2008-cluster-fails-on-windows2008-the-group-or-resource-is-not-in-the-correct-state-to-perform-the-requested-operation-exception-from-hresult-0x8007139f.aspx

Backup DataBase Users and Roles

Use the below script to back up users, roles, schema and secure permission from a DB.
1) Update the @DBNAME with the DB Name which you want to script out.
2) Update @STORAGEDBNAME with the DB Nam where you want to store the 
result.
3) Execute script in the same DB and get the result.

--select * from MSDB.DBO.TEMP_TABLE_FOR_USERS
DECLARE @DBNAME VARCHAR(50),
  @STORAGEDBNAME VARCHAR(50)
SET @DBNAME='<Your DB Name>'
SET @STORAGEDBNAME='MSDB'

SET NOCOUNT ON
DECLARE @CMD VARCHAR(350)
SET @CMD= 'IF  EXISTS (SELECT * FROM '+@STORAGEDBNAME+'.sys.objects WHERE object_id = OBJECT_ID('''+@STORAGEDBNAME+'.[dbo].[TEMP_TABLE_FOR_USERS]'')
   AND type in (''U''))
   DROP TABLE '+@STORAGEDBNAME+'.DBO.TEMP_TABLE_FOR_USERS
   CREATE TABLE '+@STORAGEDBNAME+'.DBO.TEMP_TABLE_FOR_USERS
   ([COMMAND] TEXT)'
   
EXEC (@CMD)
CREATE TABLE #TEMP
 (   NUMBER INT IDENTITY(1,1) NOT NULL, 
  USERNAME VARCHAR(100),
  ROLENAME VARCHAR(100),
  ISNTGROUP INT
 )
CREATE TABLE #TEMP1
 (   NUMBER INT IDENTITY(1,1) NOT NULL, 
  OBJECTNAME VARCHAR(100),
  TYPE_DESC VARCHAR(100),
  PERMISSION_NAME VARCHAR(100),
  STATE_DESC VARCHAR(100),
  USERNAME VARCHAR(100),
  SCHEMANAME VARCHAR(100)
 )
CREATE TABLE #TEMP2
 (   NUMBER INT IDENTITY(1,1) NOT NULL, 
  USERNAME VARCHAR(100),ISNTGROUP INT
  )

DECLARE @CM VARCHAR(100)
SET @CM='INSERT INTO '+@STORAGEDBNAME+'.DBO.TEMP_TABLE_FOR_USERS SELECT ''USE '+@DBNAME+''''
EXEC (@CM)

DECLARE @CMD1 VARCHAR(500),
@USER INT,
@USERNAME VARCHAR(50)

SET @CMD1= 'SELECT U.NAME , G.NAME ,U.ISNTGROUP
            FROM '+ @DBNAME+'.DBO.SYSUSERS U,' +@DBNAME+'.DBO.SYSUSERS G,' +@DBNAME+'.DBO.SYSMEMBERS M
            WHERE   G.UID = M.GROUPUID
            AND G.ISSQLROLE = 1 AND U.UID = M.MEMBERUID AND U.NAME<>''dbo'''
INSERT INTO #TEMP
EXEC (@CMD1)

INSERT INTO #TEMP2
SELECT DISTINCT USERNAME,ISNTGROUP FROM #TEMP
DECLARE @CMD2 VARCHAR(5000)
SET @CMD2=  'DECLARE @COUNT INT,@USER INT,@ISNT INT
     SET @COUNT=1 SELECT @USER=COUNT(*) FROM #TEMP2 
     WHILE @USER>=@COUNT
     BEGIN
       SELECT @ISNT= ISNTGROUP FROM #TEMP2 WHERE NUMBER=@COUNT
       IF @ISNT=0
       BEGIN
      INSERT INTO '+@STORAGEDBNAME+'.DBO.TEMP_TABLE_FOR_USERS
      SELECT ''IF NOT EXISTS (SELECT * FROM SYS.DATABASE_PRINCIPALS WHERE NAME = ''''''+USERNAME+'''''')
      CREATE USER [''+USERNAME+''] FOR LOGIN ['' +USERNAME +''] WITH DEFAULT_SCHEMA=[DBO]'' FROM #TEMP2
      WHERE NUMBER=@COUNT 
      END
      ELSE
      BEGIN
      INSERT INTO '+@STORAGEDBNAME+'.DBO.TEMP_TABLE_FOR_USERS
      SELECT ''IF NOT EXISTS (SELECT * FROM SYS.DATABASE_PRINCIPALS WHERE NAME = ''''''+USERNAME+'''''')
      CREATE USER [''+USERNAME+''] FOR LOGIN ['' +USERNAME +''] '' FROM #TEMP2
      WHERE NUMBER=@COUNT 
      END
      SET @COUNT=@COUNT+1
     END'
EXEC (@CMD2)
--SELECT @CMD2
-------------------- Schema Starts Here ------------------

CREATE TABLE #TEMP3
 (   NUMBER INT IDENTITY(1,1) NOT NULL, 
  SCHEMANAME VARCHAR(100)
 )
INSERT INTO #TEMP3 SELECT NAME FROM SYS.SCHEMAS WHERE [SCHEMA_ID] BETWEEN 5 AND 16383


DECLARE @CMD5 VARCHAR(5000)
SET @CMD5= 'DECLARE @SCHEMA INT,
     @COUNT3 INT
   SET @COUNT3=1
   SELECT @SCHEMA=COUNT(SCHEMANAME) FROM #TEMP3
   WHILE @SCHEMA>=@COUNT3
    BEGIN
    INSERT INTO '+@STORAGEDBNAME+'.DBO.TEMP_TABLE_FOR_USERS
      SELECT ''IF NOT EXISTS (SELECT * FROM SYS.SCHEMAS WHERE NAME = ''''''+SCHEMANAME+'''''')
      EXEC SYS.SP_EXECUTESQL N''''CREATE SCHEMA [''+SCHEMANAME+''] AUTHORIZATION ['' +SCHEMANAME +'']'''''' FROM #TEMP3
      WHERE NUMBER=@COUNT3 SET @COUNT3=@COUNT3+1
    END'
     
     
EXEC (@CMD5)

-------------------- User Role Starts Here ---------------
DECLARE @CMD3 VARCHAR(5000)
SET @CMD3= 'DECLARE @ROLE INT,
     @COUNT2 INT
   SET @COUNT2=1
   SELECT @ROLE=COUNT(ROLENAME) FROM #TEMP
   WHILE @ROLE>=@COUNT2
    BEGIN
     INSERT INTO '+@STORAGEDBNAME+'.DBO.TEMP_TABLE_FOR_USERS SELECT ''EXEC sp_addrolemember ''''''+ROLENAME+ '''''' ,''''''+ USERNAME+'''''''' FROM #TEMP 
     WHERE NUMBER=@COUNT2 SET @COUNT2=@COUNT2+1 
    END'
 
EXEC (@CMD3)

--------------------- Secure Permission Starts Here ---------------
INSERT INTO #TEMP1
SELECT O.NAME COLLATE LATIN1_GENERAL_CI_AS_KS_WS AS OBJECTNAME ,TYPE_DESC,
PERMISSION_NAME,STATE_DESC,U.NAME AS USERNAME,S.NAME AS SCHEMANAME
FROM SYS.DATABASE_PERMISSIONS P
INNER JOIN SYS.OBJECTS O ON O.OBJECT_ID=MAJOR_ID
INNER JOIN SYSUSERS U ON U.UID=P.GRANTEE_PRINCIPAL_ID
INNER JOIN SYS.SCHEMAS S ON O.SCHEMA_ID=S.SCHEMA_ID
WHERE TYPE_DESC <>'SYSTEM_TABLE'

DECLARE @CMD4 VARCHAR(5000)
SET @CMD4= 'DECLARE @SECUR INT,
     @COUNT1 INT
   SET @COUNT1=1
   SELECT @SECUR=COUNT(*) FROM #TEMP1
   WHILE @SECUR>=@COUNT1
    BEGIN
     INSERT INTO '+@STORAGEDBNAME+'.DBO.TEMP_TABLE_FOR_USERS SELECT ''''+STATE_DESC+'' ''+PERMISSION_NAME+'' ON ''+SCHEMANAME+''.''+OBJECTNAME+''  TO [''+USERNAME +'']''FROM #TEMP1 
     WHERE NUMBER=@COUNT1 SET @COUNT1=@COUNT1+1
    END'
EXEC (@CMD4)

-------------------- Final Output ---------------
DROP TABLE #TEMP
DROP TABLE #TEMP1
DROP TABLE #TEMP2
DROP TABLE #TEMP3
SET NOCOUNT OFF

Database Transaction Log Cannot be Shrunk Because it is Marked as “REPLICATION” (and Replication has not been Configured)

Finding the reason for not being able to truncate the transaction log requires using the DMV sys.databases and looking for the information under the log_reuse_wait_desc column. Most of the times the description on this column will be LOG_BACKUP, indicating that a transaction log backup operation is needed in order to truncate the transaction log file. In our case the result from this query was REPLICATION (I am using the AdventureWorks sample database here to illustrate this):
USE master;
GO
SELECT name, log_reuse_wait_desc, * FROM sys.databases
WHERE name = 'AdventureWorks';

Trying to restore the status of the transaction log file using the SQL Server Replication stored proceduresp_repldone will not fix the issue as the stored procedure will complain the database is not configured for replication. The fastest and easiest way to get rid of the REPLICATION mark in the transaction log file is to configure Snapshot replication for the database and then, afterwards, removing this configuration from the server.

Select the affected database as the database for Replication
You do not need to select all the objects for the Replication process, just select any table (one) on the database
Remember to create an initial Snapshot when asked to do so during the wizard
You can safely use the SQL Server Agent service account in the Security settings as this Replication will only be used temporarily
Once the Snapshot Replication Wizard completes the replication scenario successfully, run again the previous SELECT in sys.database; at this point the ‘log_reuse_wait_desc’ column should show either NOTHING or LOG_BACKUP:



If the status is still REPLICATION, execute the following instruction to force all pending transactions to be distributed:

EXEC sp_repldone @xactid = NULL, @xact_sgno = NULL, @numtrans = 0, @time = 0, @reset = 1;

Then we need to remove all replication objects from the database as we do not longer need any Replication for this database. First we drop the Publication (we do not need to drop subscribers firsts as we did not configured any):
USE AdventureWorks;
GO
EXEC sp_droppublication @publication = N'AW_Test_Publication'
USE master
GO
EXEC sp_replicationdboption @dbname = N'AdventureWorks', @optname = N'publish', @value = N'false';
Note that in this example our Publication name is “AW_Test_Publication”. In your case this name will vary depending on what you have specified during the Snapshot Replication Wizard.
Then we need to drop the Distributor:
USE master;
GO
exec sp_dropdistributor @no_checks = 1;
And make sure, finally, no replication objects remain on the database by running the following stored procedure:
USE master;
GO
sp_removedbreplication 'AdventureWorks';

SQL Server 2008 Configuration Manager WMI Error

I tried to open up the configuration manager but the following error showed up instead of the configuration manager mmc.
The error was “Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manager SQL Server 2005  or later servers with SQL Server Configuration Manager. Invalid class [0X80041010]”.

I followed the steps exactly as mentioned in the above blog post and it worked perfectly for SQL Server 2008 too. Here are the steps:

1. Open up command prompt using elevated privileges (Vista, Windows 7, Windows Server 2008) or normally in Windows XP or Windows 2003.
2. Navigate to the folder c:\Program Files (x86)\Microsoft SQL Server\100\Shared
3. Run the following command:  mofcomp.exe "C:\Program Files (x86)\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof"      (or just “mofcomp.exe  sqlmgmproviderxpsp2up.mof” )
4. You should see a similar output in your command prompt:

Parsing MOF file: C:\Program Files (x86)\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof

MOF file has been successfully parsed

Storing data in the repository…

Done!

Document Objects in DB

Its important that a DBA need to document all the objects in the DB for future reference. Its possible by executing the below query and save the output as html.


GO
Declare @i Int, @maxi Int
Declare @j Int, @maxj Int
Declare @sr int
Declare @Output varchar(4000)
--Declare @tmpOutput varchar(max)
Declare @last varchar(155), @current varchar(255), @typ varchar(255), @description varchar(4000), @createdat datetime, @modifiedat datetime, @objType varchar(255)
create Table #Tables  (id int identity(1, 1), Object_id int, Name varchar(155), Type varchar(20), [description] varchar(4000), created datetime, modified datetime)
create Table #Columns (id int identity(1,1), Name varchar(155), Type Varchar(155), Nullable varchar(2), [description] varchar(4000))
create Table #Fk(id int identity(1,1), Name varchar(155), col Varchar(155), refObj varchar(155), refCol varchar(155))
create Table #Constraint(id int identity(1,1), Name varchar(155), col Varchar(155), definition varchar(1000))
create Table #Indexes(id int identity(1,1), Name varchar(155), Type Varchar(25), cols varchar(1000))
create Table #ProcCode (id int identity(1,1), Name varchar(155), Type Varchar(155), Nullable varchar(2), [description] varchar(max))
create Table #Params (id int identity(1,1), Name varchar(155), Type Varchar(155), Length int, Precision int,Direction char (5))


Print '<head>'
Print '<title>::' + DB_name() + '::</title>'
Print '<style>'   
Print ' body {'
Print ' font-family:verdana;'
Print ' font-size:9pt;'
Print ' }'
Print ' td {'
Print ' font-family:verdana;'
Print ' font-size:9pt;'
Print ' }'
Print ' th {'
Print ' font-family:verdana;'
Print ' font-size:9pt;'
Print ' background:#d3d3d3;'
Print ' }'
Print ' table'
Print ' {'
Print ' background:#d3d3d3;'
Print ' }'
Print ' tr'
Print ' {'
Print ' background:#ffffff;'
Print ' }'
Print ' </style>'
Print '</head>'
Print '<body>'


set nocount on
begin
insert into #Tables (Object_id, Name, Type, [description], created, modified)
Select o.object_id,  '[' + s.name + '].[' + o.name + ']', 
case when type = 'V' then 'View' when type = 'U' then 'Table' when type = 'P' then 'Proc' end,  
cast(p.value as varchar(4000)), o.create_date, o.modify_date
from sys.objects o 
left outer join sys.schemas s on s.schema_id = o.schema_id 
left outer join sys.extended_properties p on p.major_id = o.object_id and minor_id = 0 and p.name = 'MS_Description' 
where type in ('U', 'V','P') and left(o.name,3) != 'sp_' and left(o.name,3) != 'sys'
order by type desc, o.name 
end
Set @maxi = @@rowcount
set @i = 1


print '<table width="1000"  border="0" cellspacing="0" cellpadding="0"><tr><td colspan="3" style="height:50;font-size:14pt;text-align:center;"><a name="index"></a><b>Index</b></td></tr></table>'
print '<table width="1000"  border="0" cellspacing="1" cellpadding="0"><tr><th>Sr</th><th>Object</th><th>Type</th></tr>' 
While(@i <= @maxi)
begin
select @Output =  '<tr><td align="center">' + Cast((@i) as varchar) + '</td><td><a href="#' + Type + ':' + name + '">' + name + '</a></td><td>' + Type + '</td></tr>' 
from #Tables where id = @i

print @Output
set @i = @i + 1
end
print '</table><br />'


set @i = 1
While(@i <= @maxi)
begin
--table header
select @Output =  '<tr><th align="left"><a name="' + Type + ':' + name + '"></a><b>' + Type + ':' + name + '</b></th></tr>',  @description = [description], @objType =type, @createdat = created, @modifiedat = modified
from #Tables where id = @i

print '<br /><br /><br /><table width="1000"  border="0" cellspacing="0" cellpadding="0"><tr><td align="right"><a href="#index">Index</a></td></tr>'
print @Output
print '</table><br />'
if (@objType != 'Proc')
begin
print '<table width="1000"  border="0" cellspacing="0" cellpadding="0"><tr><td><b>Description</b></td></tr><tr><td>' + isnull(@description, '') + '</td></tr></table><br />' 

--table columns
truncate table #Columns 
begin
insert into #Columns  (Name, Type, Nullable, [description])
Select c.name, 
type_name(user_type_id) + (
case when (type_name(user_type_id) = 'varchar' or type_name(user_type_id) = 'nvarchar' or type_name(user_type_id) ='char' or type_name(user_type_id) ='nchar')
then '(' + cast(max_length as varchar) + ')' 
when type_name(user_type_id) = 'decimal'  
then '(' + cast([precision] as varchar) + ',' + cast(scale as varchar)   + ')' 
else ''
end
), 
case when is_nullable = 1 then 'Y' else 'N'  end,
cast(p.value as varchar(4000))
from sys.columns c
inner join #Tables t on t.object_id = c.object_id
left outer join sys.extended_properties p on p.major_id = c.object_id and p.minor_id  = c.column_id and p.name = 'MS_Description' 
where t.id = @i and t.type != 'Proc'
order by c.column_id
end
Set @maxj =   @@rowcount
set @j = 1

print '<table width="1000"  border="0" cellspacing="0" cellpadding="0"><tr><td><b>Table Columns</b></td></tr></table>' 
print '<table width="1000"  border="0" cellspacing="1" cellpadding="0"><tr><th>Sr.</th><th>Name</th><th>Datatype</th><th>Nullable</th><th>Description</th></tr>' 

While(@j <= @maxj)
begin
select @Output = '<tr><td align="center">' + Cast((@j) as varchar) + '</td><td>' + isnull(name,'')  + '</td><td>' +  upper(isnull(Type,'')) + '</td><td align="center">' + isnull(Nullable,'N') + '</td><td>' + isnull([description],'') + '</td></tr>' 
from #Columns  where id = @j

print @Output
Set @j = @j + 1;
end


print '</table><br />'


--reference key
truncate table #FK
begin
insert into #FK  (Name, col, refObj, refCol)
select f.name, COL_NAME (fc.parent_object_id, fc.parent_column_id) , object_name(fc.referenced_object_id) , COL_NAME (fc.referenced_object_id, fc.referenced_column_id)     
from sys.foreign_keys f
inner  join  sys.foreign_key_columns  fc  on f.object_id = fc.constraint_object_id
inner join #Tables t on t.object_id = f.parent_object_id
where t.id = @i and t.type != 'Proc'
order by f.name
end

Set @maxj =   @@rowcount
set @j = 1
if (@maxj >0)
begin


print '<table width="1000"  border="0" cellspacing="0" cellpadding="0"><tr><td><b>Refrence Keys</b></td></tr></table>' 
print '<table width="1000"  border="0" cellspacing="1" cellpadding="0"><tr><th>Sr.</th><th>Name</th><th>Column</th><th>Reference To</th></tr>' 


While(@j <= @maxj)
begin


select @Output = '<tr><td align="center">' + Cast((@j) as varchar) + '</td><td>' + isnull(name,'')  + '</td><td>' +  isnull(col,'') + '</td><td>[' + isnull(refObj,'N') + '].[' +  isnull(refCol,'N') + ']</td></tr>' 
from #FK  where id = @j


print @Output
Set @j = @j + 1;
end


print '</table><br />'
end


--Default Constraints 
truncate table #Constraint
begin
insert into #Constraint  (Name, col, definition)
select c.name,  col_name(parent_object_id, parent_column_id), c.definition 
from sys.default_constraints c
inner join #Tables t on t.object_id = c.parent_object_id
where t.id = @i and t.type != 'Proc'
order by c.name
end
Set @maxj =   @@rowcount
set @j = 1
if (@maxj >0)
begin


print '<table width="1000"  border="0" cellspacing="0" cellpadding="0"><tr><td><b>Default Constraints</b></td></tr></table>' 
print '<table width="1000"  border="0" cellspacing="1" cellpadding="0"><tr><th>Sr.</th><th>Name</th><th>Column</th><th>Value</th></tr>' 


While(@j <= @maxj)
begin


select @Output = '<tr><td align="center">' + Cast((@j) as varchar) + '</td><td>' + isnull(name,'')  + '</td><td>' +  isnull(col,'') + '</td><td>' +  isnull(definition,'') + '</td></tr>' 
from #Constraint  where id = @j


print @Output
Set @j = @j + 1;
end


print '</table><br />'
end




--Check  Constraints
truncate table #Constraint
begin
insert into #Constraint  (Name, col, definition)
select c.name,  col_name(parent_object_id, parent_column_id), definition 
from sys.check_constraints c
inner join #Tables t on t.object_id = c.parent_object_id
where t.id = @i and t.type != 'Proc'
order by c.name
end
Set @maxj =   @@rowcount

set @j = 1
if (@maxj >0)
begin


print '<table width="1000"  border="0" cellspacing="0" cellpadding="0"><tr><td><b>Check  Constraints</b></td></tr></table>' 
print '<table width="1000"  border="0" cellspacing="1" cellpadding="0"><tr><th>Sr.</th><th>Name</th><th>Column</th><th>Definition</th></tr>' 


While(@j <= @maxj)
begin


select @Output = '<tr><td align="center">' + Cast((@j) as varchar) + '</td><td>' + isnull(name,'')  + '</td><td>' +  isnull(col,'') + '</td><td>' +  isnull(definition,'') + '</td></tr>' 
from #Constraint  where id = @j
print @Output 
Set @j = @j + 1;
end


print '</table><br />'
end




--Triggers 
truncate table #Constraint
begin
insert into #Constraint  (Name)
SELECT tr.name
FROM sys.triggers tr
inner join #Tables t on t.object_id = tr.parent_id
where t.id = @i and t.type != 'Proc'
order by tr.name
end
Set @maxj =   @@rowcount

set @j = 1
if (@maxj >0)
begin


print '<table width="1000"  border="0" cellspacing="0" cellpadding="0"><tr><td><b>Triggers</b></td></tr></table>' 
print '<table width="1000"  border="0" cellspacing="1" cellpadding="0"><tr><th>Sr.</th><th>Name</th><th>Description</th></tr>' 


While(@j <= @maxj)
begin
select @Output = '<tr><td align="center">' + Cast((@j) as varchar) + '</td><td>' + isnull(name,'')  + '</td><td></td></tr>' 
from #Constraint  where id = @j
print @Output 
Set @j = @j + 1;
end


print '</table><br />'
end


--Indexes 
truncate table #Indexes
begin
insert into #Indexes  (Name, type, cols)
select i.name, case when i.type = 0 then 'Heap' when i.type = 1 then 'Clustered' else 'Nonclustered' end,  col_name(i.object_id, c.column_id)
from sys.indexes i 
inner join sys.index_columns c on i.index_id = c.index_id and c.object_id = i.object_id 
inner join #Tables t on t.object_id = i.object_id
where t.id = @i and t.type != 'Proc'
order by i.name, c.column_id
end


Set @maxj =   @@rowcount

set @j = 1
set @sr = 1
if (@maxj >0)
begin


print '<table width="1000"  border="0" cellspacing="0" cellpadding="0"><tr><td><b>Indexes</b></td></tr></table>' 
print '<table width="1000"  border="0" cellspacing="1" cellpadding="0"><tr><th>Sr.</th><th>Name</th><th>Type</th><th>Columns</th></tr>' 
set @Output = ''
set @last = ''
set @current = ''
While(@j <= @maxj)
begin
select @current = isnull(name,'') from #Indexes  where id = @j
 
if @last <> @current  and @last <> ''
begin
print '<tr><td align="center">' + Cast((@sr) as varchar) + '</td><td>' + @last + '</td><td>' + @typ + '</td><td>' + @Output  + '</td></tr>' 
set @Output  = ''
set @sr = @sr + 1
end


select @Output = @Output + cols + '<br />' , @typ = type
from #Indexes  where id = @j

set @last = @current
Set @j = @j + 1;
end
if @Output <> ''
begin
print '<tr><td align="center">' + Cast((@sr) as varchar) + '</td><td>' + @last + '</td><td>' + @typ + '</td><td>' + @Output  + '</td></tr>' 
end


print '</table><br />'
end
end
if (@objType = 'Proc')
begin


print '<table width="1000" border="0" cellspacing="0" cellpadding="0"><tr><td><b>Created:</b></td><td>' + cast(@createdat as varchar)+ '</td></tr><tr><td><b>Modified:</b></td><td>' + cast(@Modifiedat as varchar)+ '</td></tr></table><br />' 


--Proc Parameters
truncate table #Params 
begin
insert into #Params  (Name, Type, Length , Precision ,Direction)
--FOR 2005
SELECT p.name AS parameter_name
,TYPE_NAME(p.user_type_id) AS parameter_type
,p.max_length
,p.precision
,case when p.is_output = '0' then 'In' when p.is_output = '1' then 'Out' when p.is_output = '2' then 'In/Out' end 
FROM sys.objects AS o
INNER JOIN sys.parameters AS p ON o.object_id = p.object_id
INNER JOIN #tables t on p.object_id = t.object_id
WHERE t.id = @i
order by p.parameter_id
end
Set @maxj =   @@rowcount
set @j = 1


print '<table width="1000" border="0" cellspacing="0" cellpadding="0"><tr><td><b>Parameters</b></td></tr></table>' 
print '<table width="1000" border="0" cellspacing="1" cellpadding="0"><tr><th>Sr.</th><th>Name</th><th>Datatype</th><th>Length</th><th>Precision</th><th>Direction</th></tr>' 

While(@j <= @maxj)
begin
select @Output = '<tr><td align="center">' + Cast((@j) as varchar) + '</td><td>' + isnull(name,'')  + '</td><td>' +  upper(isnull(Type,'')) + '</td><td align="right">' + Cast((Length) as varchar)  + '</td><td align="right">' + Cast((Precision) as varchar)  + '</td><td align="center">' + Direction + '</td></tr>' 
from #Params  where id = @j

print @Output
Set @j = @j + 1;
end


print '</table><br />'



--table columns
truncate table #ProcCode 
begin
insert into #ProcCode  ([description])
--FOR 2005
SELECT replace(replace(m.definition,char(13),'</br>'),' ', '&nbsp;')
  FROM sys.sql_modules m, sys.procedures p, #tables t
  WHERE m.object_id = p.object_id 
  and p.object_id = t.object_Id
  and t.id = @i
end
Set @maxj =   @@rowcount
set @j = 1


print '<table width="1000" border="0" cellspacing="0" cellpadding="0"><tr><td><b>Code</b></td></tr></table>' 
print '<table width="1000" border="0" cellspacing="1" cellpadding="0"><tr><th>Description</th></tr>' 

While(@j <= @maxj)
begin
select @Output = '<tr><td>' + isnull([description],'') + '</td></tr>' 
from #ProcCode  where id = @j

print @Output
Set @j = @j + 1;
end


print '</table><br />'




end
    Set @i = @i + 1;
end


Print '</body>'
Print '</html>'


drop table #Tables
drop table #Columns
drop table #FK
drop table #Constraint
drop table #Indexes 
drop table #Params
drop table #ProcCode


set nocount off