Internal structure

Get all databases on the server

SELECT name FROM Master.sys.Databases
or: sp_helpdb

Get all tables in a database

SELECT Table_Name, Table_Schema FROM information_schema.Tables
or: sp_tables (this shows all tables, also system tables)

Get structure of a table

SELECT ordinal_position, column_name, data_type, is_nullable, character_maximum_length, column_default
FROM information_schema.Columns
WHERE table_name = 'xx' AND table_schema = 'sc'

Get constraints

select a.constraint_name, a.table_name, a.column_name, a.ordinal_position, b.constraint_type
from information_schema.KEY_COLUMN_USAGE a
inner join information_schema.TABLE_CONSTRAINTS b
on a.constraint_name = b.constraint_name

Get all stored procedures in a database

select * from information_schema.routines

Change schema of a table

alter schema newSchemaname transfer oldSchemaname.tablename



View locked table: SELECT QuoteID FROM Quote WITH (nolock)
View locks: sp_lock (this gives the locked table as an objectid)
Look up objectid: select * from sys.all_objects where object_id = …
Remove lock: kill 54 (spid)


Remove lock: kill (spid: first column)

SQL server profiler

Interesting columns: TextData, DatabaseName, LoginName (with domain), NTUserName (without domain), ObjectName, StartTime, EndTime

For stored procedures, ObjectName = 'sp_executesql'

Filter ApplicationName != 'Report Server'

If you connect from .NET, the application name will be .NET SqlClient Data Provider

Linked servers

sp_linkedservers: shows all the linked servers

sp_tables_ex LinkedServerName: shows the tables in the linked server

Change the physical filenames of a database

After taking the database offline, you also manually have to rename the files…
alter database PfeBgsTestContract_orig set offline

ALTER DATABASE PfeBgsTestContract_orig MODIFY FILE (NAME = logicalName, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2005\MSSQL\DATA\PfeBgsTestContract_orig.mdf')

ALTER DATABASE PfeBgsTestContract_orig MODIFY FILE (NAME = logicalName_log, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2005\MSSQL\DATA\PfeBgsTestContract_orig_log.ldf')

alter database PfeBgsTestContract_orig set online

Also see:
to rename the logical names.

Retrieve SQL logins and permissions

Note that it only queries the roles, not the individual given permissions, such as select on a table or execute on a stored procedure.
Master is included because it grants permissions for all databases within the server.

[UserName] SYSNAME,
[LoginType] SYSNAME,
[AssociatedRole] VARCHAR(MAX),
[create_date] DATETIME,
[modify_date] DATETIME

EXEC sp_MSforeachdb 'use [?]
SELECT ''?'' [DB_Name],
CASE [prin].[name] WHEN ''dbo'' THEN [prin].[name] + '' (''+ (SELECT SUSER_SNAME(owner_sid) FROM [master].[sys].[databases] WHERE name =''?'') + '')'' ELSE [prin].[name] END [UserName],
[prin].[type_desc] [LoginType], ISNULL(USER_NAME([mem].[role_principal_id]),'''') [AssociatedRole], [create_date], [modify_date]
FROM [sys].[database_principals] [prin]
LEFT OUTER JOIN [sys].[database_role_members] [mem] ON [prin].[principal_id] = [mem].[member_principal_id]
WHERE [prin].[sid] IS NOT NULL
AND [prin].[sid] NOT IN (0x00)
AND [prin].[is_fixed_role] <> 1
AND [prin].[name] NOT LIKE ''##%'';';

SELECT [DBName], [UserName], [LoginType], [create_date], [modify_date],
STUFF((SELECT ',' + CONVERT(VARCHAR(500),[associatedrole])
FROM @DB_USers [user2]
WHERE [user1].[DBName] = [user2].[DBName] AND [user1].[UserName] = [user2].[UserName]
FOR XML PATH('')), 1, 1, '') AS [Permissions_user]
FROM @DB_USers [user1]
WHERE [DBName] IN ('master', 'name of database you want to check')
GROUP BY [DBName], [username], [logintype], [create_date], [modify_date]
ORDER BY [username], [DBName];

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License