Data Type

"P" means "pointer"
"W" means "wide"
"STR" means "string"
"C" means "const"


PWSTR means "pointer of wide string", that is, unsigned short* (or wchar_t*)

LPSTR means "long pointer of string", that is, char*

WCHAR means "wide char", that is, unsigned short (or wchar_t)

LPCSTR means "const pointer of string", that is, const char*

PWSTR is a pointer to a UNICODE string.
[ typedef WCHAR* PWSTR; typedef wchar_t WCHAR; ]

LPSTR is a pointer to a 8 bit ANSI string
[typedef CHAR* LPSTR; typedef char CHAR; ]

WCHAR is a wchar_t as mentioned above and LPCSTR is an 8 bit ANSI string
[ typedef __nullterminated CONST CHAR* LPCSTR; ]

These typedefs can be found in file WinNT.h



Char/wchar/TCHAR : The C strings for ANSI and Unicode

CString : The C++/MFC class wrapper for C strings

BSTR : The Visual Basic string type

_bstr_t : A C++ class wrapper for the Visual Basic string type

CComBSTR : Yet another C++ class wrapper for the Visual Basic

string type used predominately in ATL code

Most popular Used Regular Expression

Internet Email: \w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*
Internet URL: http(s)?://([\w-]+\.)+[\w-]+(/[\w- ./?%&=]*)?
US Phone Number: ((\(\d{3}\) ?)|(\d{3}-))?\d{3}-\d{4}
US Social Security Number: \d{3}-\d{2}-\d{4}
US ZIP Code: \d{5}(-\d{4})?
URI: \b(\S+)://([^:]+)(?::(\S+))?\b

List of Querying the SQL Server System Catalog

How do I find the data types of the columns of a specified table?
Before you run the following query, replace and with valid names.

USE ;
GO
SELECT c.name AS column_name
,c.column_id
,SCHEMA_NAME(t.schema_id) AS type_schema
,t.name AS type_name
,t.is_user_defined
,t.is_assembly_type
,c.max_length
,c.precision
,c.scale
FROM sys.columns AS c
JOIN sys.types AS t ON c.user_type_id=t.user_type_id
WHERE c.object_id = OBJECT_ID('')
ORDER BY c.column_id;
GO

How do I find all the owners of entities contained in a specified schema?

USE ;
GO
SELECT 'OBJECT' AS entity_type
,USER_NAME(OBJECTPROPERTY(object_id, 'OwnerId')) AS owner_name
,name
FROM sys.objects WHERE SCHEMA_NAME(schema_id) = ''
UNION
SELECT 'TYPE' AS entity_type
,USER_NAME(TYPEPROPERTY(SCHEMA_NAME(schema_id) + '.' + name, 'OwnerId')) AS owner_name
,name
FROM sys.types WHERE SCHEMA_NAME(schema_id) = ''
UNION
SELECT 'XML SCHEMA COLLECTION' AS entity_type
,COALESCE(USER_NAME(xsc.principal_id),USER_NAME(s.principal_id)) AS owner_name
,xsc.name
FROM sys.xml_schema_collections AS xsc JOIN sys.schemas AS s
ON s.schema_id = xsc.schema_id
WHERE s.name = '';
GO

How do I find all the tables that do not have a primary key?

USE ;
GO
SELECT SCHEMA_NAME(t.schema_id) AS schema_name
,t.name AS table_name
FROM sys.tables t
WHERE object_id NOT IN
(
SELECT parent_object_id
FROM sys.key_constraints
WHERE type_desc = 'PRIMARY_KEY_CONSTRAINT' -- or type = 'PK'
);
GO

Or, you can run the following query.

USE ;
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

How do I find all the tables that do not have an index?

USE ;
GO
SELECT SCHEMA_NAME(schema_id) AS schema_name
,name AS table_name
FROM sys.tables
WHERE OBJECTPROPERTY(object_id,'IsIndexed') = 0
ORDER BY schema_name, table_name;
GO

How do I find all the tables that have an identity column?

USE ;
GO
SELECT SCHEMA_NAME(schema_id) AS schema_name
, t.name AS table_name
, c.name AS column_name
FROM sys.tables AS t
JOIN sys.identity_columns c ON t.object_id = c.object_id
ORDER BY schema_name, table_name;
GO

Or, you can run the following query.
USE ;
GO
SELECT SCHEMA_NAME(schema_id) AS schema_name
,name AS table_name
FROM sys.tables
WHERE OBJECTPROPERTY(object_id,'TableHasIdentity') = 1
ORDER BY schema_name, table_name;
GO

How do I find all the stored procedures in a database?

USE ;
GO
SELECT name AS procedure_name
,SCHEMA_NAME(schema_id) AS schema_name
,type_desc
,create_date
,modify_date
FROM sys.procedures;
GO

How do I find the parameters for a specified stored procedure or function?

USE ;
GO
SELECT SCHEMA_NAME(schema_id) AS schema_name
,o.name AS object_name
,o.type_desc
,p.parameter_id
,p.name AS parameter_name
,TYPE_NAME(p.user_type_id) AS parameter_type
,p.max_length
,p.precision
,p.scale
,p.is_output
FROM sys.objects AS o
INNER JOIN sys.parameters AS p ON o.object_id = p.object_id
WHERE o.object_id = OBJECT_ID('')
ORDER BY schema_name, object_name, p.parameter_id;
GO

How do I find all the user-defined functions in a database?

USE ;
GO
SELECT name AS function_name
,SCHEMA_NAME(schema_id) AS schema_name
,type_desc
,create_date
,modify_date
FROM sys.objects
WHERE type_desc LIKE '%FUNCTION%';
GO

How do I find all views in a database?

USE ;
GO
SELECT name AS view_name
,SCHEMA_NAME(schema_id) AS schema_name
,OBJECTPROPERTYEX(object_id,'IsIndexed') AS IsIndexed
,OBJECTPROPERTYEX(object_id,'IsIndexable') AS IsIndexable
,create_date
,modify_date
FROM sys.views;
GO

How do I find all the entities that have been modified in the last N days?

USE ;
GO
SELECT name AS object_name
,SCHEMA_NAME(schema_id) AS schema_name
,type_desc
,create_date
,modify_date
FROM sys.objects
WHERE modify_date > GETDATE() -
ORDER BY modify_date;
GO

How do I view the definition of a server-level trigger?

SELECT definition
FROM sys.server_sql_modules;
GO

How do I find the columns of a primary key for a specified table?

USE ;
GO
SELECT i.name AS index_name
,ic.index_column_id
,key_ordinal
,c.name AS column_name
,TYPE_NAME(c.user_type_id)AS column_type
,is_identity
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
INNER JOIN sys.columns AS c
ON ic.object_id = c.object_id AND c.column_id = ic.column_id
WHERE i.is_primary_key = 1
AND i.object_id = OBJECT_ID('');
GO

Or, you can use the COL_NAME function as shown in the following example.

USE ;
GO
SELECT i.name AS index_name
,COL_NAME(ic.object_id,ic.column_id) AS column_name
,ic.index_column_id
,key_ordinal
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
AND i.object_id = OBJECT_ID('');
GO

How do I find the columns of a foreign key for a specified table?

USE ;
GO
SELECT
f.name AS foreign_key_name
,OBJECT_NAME(f.parent_object_id) AS table_name
,COL_NAME(fc.parent_object_id, fc.parent_column_id) AS constraint_column_name
,OBJECT_NAME (f.referenced_object_id) AS referenced_object
,COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS referenced_column_name
,is_disabled
,delete_referential_action_desc
,update_referential_action_desc
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.object_id = fc.constraint_object_id
WHERE f.parent_object_id = OBJECT_ID('');

How do I determine if a column is used in a computed column expression?

USE ;
GO
SELECT OBJECT_NAME(object_id) AS object_name
,COL_NAME(object_id, column_id) AS computed_column
,class_desc
,is_selected
,is_updated
,is_select_all
FROM sys.sql_dependencies
WHERE referenced_major_id = OBJECT_ID('')
AND referenced_minor_id = COLUMNPROPERTY(referenced_major_id, '', 'ColumnId')
AND class = 1;
GO

How do I find all the columns that are used in a computed column expression?
USE ;
GO
SELECT OBJECT_NAME(d.referenced_major_id) AS object_name
,COL_NAME(d.referenced_major_id, d.referenced_minor_id) AS column_name
,OBJECT_NAME(referenced_major_id) AS dependent_object_name
,COL_NAME(d.object_id, d.column_id) AS dependent_computed_column
,cc.definition AS computed_column_definition
FROM sys.sql_dependencies AS d
JOIN sys.computed_columns AS cc
ON cc.object_id = d.object_id AND cc.column_id = d.column_id AND d.object_id=d.referenced_major_id
WHERE d.class = 1
ORDER BY object_name, column_name;
GO

How do I find all the constraints for a specified table?

USE ;
GO
SELECT OBJECT_NAME(object_id) as constraint_name
,SCHEMA_NAME(schema_id) AS schema_name
,OBJECT_NAME(parent_object_id) AS table_name
,type_desc
,create_date
,modify_date
,is_ms_shipped
,is_published
,is_schema_published
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT'
AND parent_object_id = OBJECT_ID('');
GO

How do I find all the indexes for a specified table?

USE ;
GO
SELECT i.name AS index_name
,i.type_desc
,is_unique
,ds.type_desc AS filegroup_or_partition_scheme
,ds.name AS filegroup_or_partition_scheme_name
,ignore_dup_key
,is_primary_key
,is_unique_constraint
,fill_factor
,is_padded
,is_disabled
,allow_row_locks
,allow_page_locks
FROM sys.indexes AS i
INNER JOIN sys.data_spaces AS ds ON i.data_space_id = ds.data_space_id
WHERE is_hypothetical = 0 AND i.index_id <> 0
AND i.object_id = OBJECT_ID('');
GO

Information related to the security admin server role and system admin server role

Security is a very important aspect that a DBA should know about. It is also important to know which login has a sysadmin or security admin server level role. The following SQL Command will show information related to the security admin server role and system admin server role.

SELECT l.name, l.denylogin, l.isntname, l.isntgroup, l.isntuser
FROM master.dbo.syslogins l
WHERE l.sysadmin = 1 OR l.securityadmin = 1

Server level Configuration Information

Server level configuration controls some of the features and performance of SQL Server. It is also important for a SQL Server DBA to know the server level configuration information. The following SQL Statement will give all of the information related to Server level configuration.

SELECT * from sys.configurations order by NAMEIf you are using SQL Server 2000, you can execute the following command instead.

SP_CONFIGURE 'show advanced options',1
go
RECONFIGURE with OVERRIDE
go
SP_CONFIGURE
go