List all Tables in Database With / Without Primary Key

USE <DatabaseName>;
GO
SELECT SCHEMA_NAME(schema_id) AS Schema_Name, name AS Table_Name
FROM sys.tables
WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasPrimaryKey') = 0
ORDER BY Schema_Name, Table_Name;
GO

SELECT i.name AS Index_Name,OBJECT_NAME(ic.OBJECT_ID) AS Table_Name,COL_NAME(ic.OBJECT_ID,ic.column_id) AS Column_Name
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic
ON i.OBJECT_ID = ic.OBJECT_ID
AND i.index_id = ic.index_id
WHERE i.is_primary_key = 1

Database 'msdb' cannot be opened.

Today I am getting the below error while querying Database:
Database 'msdb' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information. (Microsoft SQL Server, Error: 926)


I searched the net and found the below solution: 
In SQL Server 2005 & Above
steps need to be run and taken if DB is in Suspect Mode.


EXEC sp_resetstatus 'Database_Name';
ALTER DATABASE Database_Name SET EMERGENCY
DBCC checkdb('Database_Name')
ALTER DATABASE Database_Name SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('Database_Name', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE Database_Name SET MULTI_USER


In SQL Server 2000
USE Master
GO
-- Determine the original database status
SELECT [Name], DBID, Status FROM master.dbo.sysdatabases
GO
-- Enable system changes
sp_configure 'allow updates',1
GO
RECONFIGURE WITH OVERRIDE
GO
-- Update the database status
UPDATE master.dbo.sysdatabases
SET Status = 8 or 32768 --8 means online & 32768 means emergency
WHERE [Name] = 'Database_Name'
GO
-- Disable system changes
sp_configure 'allow updates',0
GO
RECONFIGURE WITH OVERRIDE
GO
-- Determine the final database status
SELECT [Name], DBID, Status
FROM master.dbo.sysdatabases
GO

SQL Server Configuration Manager – Cannot connect to WMI provider – Invalid class [0x80041010]

Today when I was trying to connect to my SQL Server 2008 using SQL Server Configuration Manager, the following error dialog pops up:

Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager. Invalid class [0x80041010]

I have searched the netand the following code saves me.

>> Open an elevated Command Prompt, and type the following…

mofcomp.exe "C:\Program Files (x86)\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof"

 

List the modified objects in SQL Server

For viewing the modified date of Stored Procs and UDF alone:

Select
Routine_name, Routine_Type, Created, Last_altered
From Information_schema.routines
Where
Routine_type in ('PROCEDURE', 'FUNCTION')
Order by
Last_altered desc, Routine_type, Routine_name 

For viewing the modified date of Stored Procs, UDF and Views:

We can query 'Sys.Objects' table and find out the list of Stored procs, UDFs, Views etc., which have got modified.

Code snippet:
Select
[name] as 'Object Name',
[type] as 'Object Type',
create_date,
modify_date
From sys.objects
Where [type] in ('P', 'FN', 'TF', 'V')
Order by Modify_Date desc, [type], [name]

The above query will list all 'SPs', 'UDFs' and 'Views' in the current database with its 'Created date' and 'Modified date'. We can further finetune this code to match our exact need!

For triggers
Check out create_date and modify_date columns in sys.triggers.
select * from sys.triggers

Resetting user names for orphaned logins

Sometime when we restore the database from a backup file in SQL Server, after that some logins are not allowed access to database. error like can’t login user ‘null’ in the database occur when connecting using code.

This happens because while restoring the database backup the login was not linked with the user name it was associated in the database whose backup we are using. To login into the DB using that login we need to assign that login a username.

@Office I need to restore databases many times and use them to debug to solve the issue. So I used to get this problem every time I restore the backup. Earlier to solve this issue, I used to use brute force method to resolve this like deleting the troubled login from the “Database\Security\Users” and deleting the schema related to this login. Then adding the same login again to “Database\Security\Users” . This used to solve my problem.

Microsoft SQL Server provides a procedure to solve this issue, which is in fact the right way to it. sp_change_users_login this sp is intended to relink the orphaned login with their user name. Following are the parameters to this procedure.

These parameters indicate what action to perform. It can be one of the 3 values

• Reports : Returns all the orphaned logins in the database
• Auto_Fix: Fixes the given login and sets the username to the user with the same name in database, if a user with same name is not found then a new entry in sys.database_principals table and the newly created entry is linked with the provided login.
• Update_One: updates the given login with the login name provided.

USE Demo_DB

EXEC SP_Change_User_Login 'Report'

The Report argument will return any orphaned users within the current database.

USE Demo_DB

EXEC SP_Change_User_Login 'Auto_Fix', 'SampleUser'

Above example displays usage of Auto_fix

USE Demo_DB

EXEC SP_Change_User_Login 'Update_One', 'SampleUser', 'SampleUser'

The above command will update the sampleuser user’s login to sampleUser.

Following are some references:

http://technet.microsoft.com/en-us/library/ms174378.aspx

http://blogs.msdn.com/blakhani/archive/2008/02/25/script-map-all-orphan-users-to-logins-else-create-login-and-map.aspx