Monday, March 26, 2012

some important system tables and procedures

some system tables

select * from sys.database_recovery_status

select * from DBROLES
--gives info about the database roles in each database

select * from sys.identity_columns
-- lists down all the identity columns in your database

select * from information_schema.KEY_COLUMN_USAGE
-- lists down all the primary keys in that database

select * from information_schema.ROUTINES
-- lists down all the SPs and functions accessed by the current user in that database

select * from information_schema.SCHEMATA
-- lists all the schemas in the database

 Name: sp_add_data_file_recover_suspect_db 
 Purpose: Adds a data file to a suspect database and runs  recovery on the database.  This SP should only be used   on databases that have been marked suspect due to  insufficient data (error 1105) or log (error 9002) space. 

Name: sp_add_log_file_recover_suspect_db 


--sp_attach_single_file_db
used to attach databases when there is no log file and only data file exists.

sp_changegroup
-- used to move a user from one role to another

sp_clean_db_file_free_space
this internally executes dbcc cleanpage(dbid,fileid,pageno) for each page.
This procedure frees up each page in the specificed file and in the specified database.

sp_clean_db_free_space


sp_databases
lists all the databases, this is similar to sp_helpdb

sp_depends <objectname>
this is used to get dependencies of a particular object in that database.
you can also get this information from the below table
select * from sysdepends


sp_helpsort
this will give the collation setting

sp_helpstats
stats for the particular object

sp_server_info

Database versions in sql server

This is something interesting that I noticed recently.

select name,cmptlevel,version from sysdatabases
the above query shows how the versions are defined between sql server 2000, 2005, 2008, 2008 R2

in 2000 - version is 539 in sysdatabases sytem table
in 2005 - version is 611 in sysdatabases sytem table
in 2008 - version is 655 in sysdatabases sytem table
in 2008R2 - version is 661 in sysdatabases sytem table

When you restore a sql server 2000 backup on to a 2008R2 server, here is the what it shows in the output file:

Database 'database_test' running the upgrade step from version 539 to version 551.
Database 'database_test' running the upgrade step from version 551 to version 552.
Database 'database_test' running the upgrade step from version 552 to version 611.
Database 'database_test' running the upgrade step from version 611 to version 621.
Database 'database_test' running the upgrade step from version 621 to version 622.
Database 'database_test' running the upgrade step from version 622 to version 625.
Database 'database_test' running the upgrade step from version 625 to version 626.
Database 'database_test' running the upgrade step from version 626 to version 627.
Database 'database_test' running the upgrade step from version 627 to version 628.
Database 'database_test' running the upgrade step from version 628 to version 629.
Database 'database_test' running the upgrade step from version 629 to version 630.
Database 'database_test' running the upgrade step from version 630 to version 631.
Database 'database_test' running the upgrade step from version 631 to version 632.
Database 'database_test' running the upgrade step from version 632 to version 633.
Database 'database_test' running the upgrade step from version 633 to version 634.
Database 'database_test' running the upgrade step from version 634 to version 635.
Database 'database_test' running the upgrade step from version 635 to version 636.
Database 'database_test' running the upgrade step from version 636 to version 637.
Database 'database_test' running the upgrade step from version 637 to version 638.
Database 'database_test' running the upgrade step from version 638 to version 639.
Database 'database_test' running the upgrade step from version 639 to version 640.
Database 'database_test' running the upgrade step from version 640 to version 641.
Database 'database_test' running the upgrade step from version 641 to version 642.
Database 'database_test' running the upgrade step from version 642 to version 643.
Database 'database_test' running the upgrade step from version 643 to version 644.
Database 'database_test' running the upgrade step from version 644 to version 645.
Database 'database_test' running the upgrade step from version 645 to version 646.
Database 'database_test' running the upgrade step from version 646 to version 647.
Database 'database_test' running the upgrade step from version 647 to version 648.
Database 'database_test' running the upgrade step from version 648 to version 649.
Database 'database_test' running the upgrade step from version 649 to version 650.
Database 'database_test' running the upgrade step from version 650 to version 651.
Database 'database_test' running the upgrade step from version 651 to version 652.
Database 'database_test' running the upgrade step from version 652 to version 653.
Database 'database_test' running the upgrade step from version 653 to version 654.
Database 'database_test' running the upgrade step from version 654 to version 655.
Database 'database_test' running the upgrade step from version 655 to version 660.
Database 'database_test' running the upgrade step from version 660 to version 661.

 



 

Sunday, March 25, 2012

how to capture object permissions in the database in sql server

Here is the correct one:

select * from sys.database_permissions would give the correct permissions for all the users and all the objects.
Here is a sample query to list all the permissions;
i got this from the following url:
http://consultingblogs.emc.com/jamiethomson/archive/2007/02/09/SQL-Server-2005_3A00_-View-all-permissions--_2800_2_2900_.aspx


select USER_NAME(p.grantee_principal_id) AS principal_name,
        dp.type_desc AS principal_type_desc,
        p.class_desc,
        OBJECT_NAME(p.major_id) AS object_name,
        p.permission_name,
        p.state_desc AS permission_state_desc
from    sys.database_permissions p
inner   JOIN sys.database_principals dp
on     p.grantee_principal_id = dp.principal_id


The below one isn't the correct one, as per MSDN:
 http://msdn.microsoft.com/en-us/library/ms186233.aspx

"Returns one row for each table privilege that is granted to or granted by the current user in the current database"

use dbname
go
select * from information_schema.TABLE_PRIVILEGES

GRANTOR    GRANTEE    TABLE_CATALOG    TABLE_SCHEMA    TABLE_NAME    PRIVILEGE_TYPE    IS_GRANTABLE
dbo          user1         db1                  schema1        table1             DELETE        NO
dbo          user2         db2                  schema2        table2             INSERT         NO

object ids of system objects in sql server

Try this:
in sql server 2005
use master
go
select * from sysobjects where id<0
total 1773 rows returned and the ids are like the below: But why do the system objects have -ve ids???
-1042000603
-1041222755
-1037786908
..
..
Also fire the below query
select xtype,count(xtype) from sysobjects where id<0 group by xtype
the result is:
xtype    (No column name)
FN    27
IF    19
P     1277
PC    3
TF    12
V     286
X     149

in sql server 2008
select * from sysobjects where id<0
total rows returned are 1927

select xtype,count(xtype) from sysobjects where id<0 group by xtype
xtype    (No column name)
AF    1
FN    38
FS    4
IF    30
P     1348
PC    3
TF    13
V     354
X     136
 
in sql server 2008R2

select * from sysobjects where id<0 
 total rows returned are 1928

select xtype,count(xtype) from sysobjects where id<0 group by xtype
order by xtype
 
xtype    (No column name)
AF    1
FN    38
FS    4
IF    30
P     1348
PC    3
TF    13
V     354
X     137

how to refresh a database in sql server

Simple steps:

1) Take the backup of the source db
2) copy the backup
3) refresh the db at the destination; while refreshing if you haven't taken a backup of the destination db, it would prompt you that the tail of the log for this db hasn't been backed up and wouldn't allow you to restore; In that case use "replace" option in your restore database script.
4) After the restoration fix the logins:
use <dbname>
sp_change_users_login 'report'
go
once you get the logins which need to be fixed use this:
sp_change_users_login 'auto_fix','loginname'

If you don't have logins for the db users then they couldn't be fixed until  you create the logins.
Also you can't fix logins for windows users, you have to manually do it.