Archive for the ‘SQL Server’ Category
Tuesday, October 21st, 2008
-- Identifies user defined datatype accessible to current user
CREATE VIEW INFORMATION_SCHEMA.DOMAINS
AS
SELECT
db_name() AS DOMAIN_CATALOG,
schema_name(schema_id) AS DOMAIN_SCHEMA,
name AS DOMAIN_NAME,
type_name(system_type_id) AS DATA_TYPE,
convert(int, TypePropertyEx(user_type_id, 'charmaxlen')) AS CHARACTER_MAXIMUM_LENGTH,
convert(int, TypePropertyEx(user_type_id, 'octetmaxlen')) AS CHARACTER_OCTET_LENGTH,
convert(sysname, null) AS COLLATION_CATALOG,
convert(sysname, null) AS COLLATION_SCHEMA,
collation_name ...
Posted in SQL Server | No Comments »
Tuesday, October 21st, 2008
-- Identifies columns which have constrained keys
CREATE VIEW INFORMATION_SCHEMA.KEY_COLUMN_USAGE
AS
SELECT
db_name() AS CONSTRAINT_CATALOG,
schema_name(f.schema_id) AS CONSTRAINT_SCHEMA,
f.name AS CONSTRAINT_NAME,
db_name() AS TABLE_CATALOG,
schema_name(p.schema_id) AS TABLE_SCHEMA,
p.name AS TABLE_NAME,
col_name(k.parent_object_id, k.parent_column_id)
AS COLUMN_NAME,
k.constraint_column_id AS ORDINAL_POSITION
FROM
sys.foreign_keys f JOIN sys.foreign_key_columns k
ON k.constraint_object_id = f.object_id
JOIN ...
Posted in SQL Server | No Comments »
Tuesday, October 21st, 2008
CREATE VIEW INFORMATION_SCHEMA.PARAMETERS
AS
SELECT
db_name() AS SPECIFIC_CATALOG,
schema_name(o.schema_id) AS SPECIFIC_SCHEMA,
o.name AS SPECIFIC_NAME,
c.parameter_id AS ORDINAL_POSITION,
convert(nvarchar(10), CASE
WHEN c.parameter_id = 0 THEN 'OUT'
WHEN c.is_output = 1 THEN 'INOUT'
ELSE 'IN' END) AS PARAMETER_MODE,
convert(nvarchar(10), CASE WHEN
c.parameter_id = 0 ...
Posted in SQL Server | No Comments »
Tuesday, October 21st, 2008
-- Identifies foreign constraints
CREATE VIEW INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
AS
SELECT
db_name() AS CONSTRAINT_CATALOG,
schema_name(f.schema_id) AS CONSTRAINT_SCHEMA,
f.name AS CONSTRAINT_NAME,
db_name() AS UNIQUE_CONSTRAINT_CATALOG,
schema_name(t.schema_id) AS UNIQUE_CONSTRAINT_SCHEMA,
i.name AS UNIQUE_CONSTRAINT_NAME,
convert(varchar(7), 'SIMPLE') AS MATCH_OPTION,
convert(varchar(11), CASE f.update_referential_action
WHEN 0 THEN 'NO ACTION'
WHEN 1 THEN 'CASCADE'
WHEN 2 THEN 'SET NULL'
WHEN ...
Posted in SQL Server | No Comments »
Tuesday, October 21st, 2008
CREATE VIEW INFORMATION_SCHEMA.ROUTINES
AS
SELECT
db_name() AS SPECIFIC_CATALOG,
schema_name(o.schema_id) AS SPECIFIC_SCHEMA,
o.name AS SPECIFIC_NAME,
db_name() AS ROUTINE_CATALOG,
schema_name(o.schema_id) AS ROUTINE_SCHEMA,
o.name AS ROUTINE_NAME,
convert(nvarchar(20), CASE
WHEN o.type IN ('P','PC')
THEN 'PROCEDURE'
ELSE 'FUNCTION' END) AS ROUTINE_TYPE,
convert(sysname, null) AS MODULE_CATALOG,
convert(sysname, null) ...
Posted in SQL Server | No Comments »
Tuesday, October 21st, 2008
CREATE VIEW INFORMATION_SCHEMA.ROUTINE_COLUMNS
AS
SELECT
db_name() AS TABLE_CATALOG,
schema_name(o.schema_id) AS TABLE_SCHEMA,
o.name AS TABLE_NAME,
c.name AS COLUMN_NAME,
c.column_id AS ORDINAL_POSITION,
convert(nvarchar(4000), null) AS COLUMN_DEFAULT,
convert(varchar(3),
CASE WHEN c.is_nullable = 1 THEN 'YES'
ELSE 'NO' END) ...
Posted in SQL Server | No Comments »
Tuesday, October 21st, 2008
-- Identifies schemas contained IN current databases
CREATE VIEW INFORMATION_SCHEMA.SCHEMATA
AS
SELECT
db_name() AS CATALOG_NAME,
name AS SCHEMA_NAME,
user_name(principal_id) AS SCHEMA_OWNER,
convert(sysname, null) AS DEFAULT_CHARACTER_SET_CATALOG,
convert(sysname, null) AS DEFAULT_CHARACTER_SET_SCHEMA,
convert(sysname, CollationPropertyFromId(-1, 'sqlcharsetname'))
AS DEFAULT_CHARACTER_SET_NAME
FROM
sys.schemas
------------
Posted in SQL Server | No Comments »
Tuesday, October 21st, 2008
-- Identifies privileges granted to or by the current user
CREATE VIEW INFORMATION_SCHEMA.TABLE_PRIVILEGES
AS
SELECT
user_name(p.grantor_principal_id) AS GRANTOR,
user_name(p.grantee_principal_id) AS GRANTEE,
db_name() AS TABLE_CATALOG,
schema_name(o.schema_id) AS TABLE_SCHEMA,
o.name AS TABLE_NAME,
convert(varchar(10),
CASE p.type
WHEN 'RF' THEN 'REFERENCES'
WHEN 'SL' THEN 'SELECT'
WHEN 'IN' THEN 'INSERT'
WHEN 'DL' THEN 'DELETE'
WHEN 'UP' THEN 'UPDATE'
END) AS ...
Posted in SQL Server | No Comments »
Tuesday, October 21st, 2008
-- Identifies table constraints for tables where the current user has any permission
CREATE VIEW INFORMATION_SCHEMA.TABLE_CONSTRAINTS
AS
SELECT
db_name() AS CONSTRAINT_CATALOG,
schema_name(c.schema_id) AS CONSTRAINT_SCHEMA,
c.name AS CONSTRAINT_NAME,
db_name() AS TABLE_CATALOG,
schema_name(t.schema_id) AS TABLE_SCHEMA,
t.name AS TABLE_NAME,
CASE c.type
WHEN 'C ' THEN 'CHECK'
WHEN 'UQ' THEN 'UNIQUE'
WHEN 'PK' THEN 'PRIMARY KEY'
WHEN 'F ...
Posted in SQL Server | No Comments »
Tuesday, October 21st, 2008
-- Identifies tables accessible to the current user
CREATE VIEW INFORMATION_SCHEMA.TABLES
AS
SELECT
db_name() AS TABLE_CATALOG,
s.name AS TABLE_SCHEMA,
o.name AS TABLE_NAME,
CASE o.type
WHEN 'U' THEN 'BASE TABLE'
WHEN 'V' THEN 'VIEW'
END AS TABLE_TYPE
FROM
sys.objects o LEFT JOIN sys.schemas s
ON s.schema_id = o.schema_id
WHERE
o.type IN ('U', 'V')
ɮtşꛀƱŸ£ v.object_id
JOIN sys.objects ...
Posted in SQL Server | No Comments »