Archive for the ‘SQL Server’ Category
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 »
Monday, October 20th, 2008
-- Identifies columns that have a user defined datatype
CREATE VIEW INFORMATION_SCHEMA.COLUMN_DOMAIN_USAGE
AS
SELECT
db_name() AS DOMAIN_CATALOG,
schema_name(t.schema_id) AS DOMAIN_SCHEMA,
t.name AS DOMAIN_NAME,
db_name() AS TABLE_CATALOG,
schema_name(o.schema_id) AS TABLE_SCHEMA,
o.name AS TABLE_NAME,
c.name AS COLUMN_NAME
FROM
sys.objects o JOIN ...
Posted in SQL Server | No Comments »
Monday, October 20th, 2008
-- Identifies views AND columns used IN their definition
CREATE VIEW INFORMATION_SCHEMA.VIEW_COLUMN_USAGE
AS
SELECT
db_name() AS VIEW_CATALOG,
schema_name(v.schema_id) AS VIEW_SCHEMA,
v.name AS VIEW_NAME,
db_name() AS TABLE_CATALOG,
schema_name(t.schema_id) AS TABLE_SCHEMA,
t.name AS TABLE_NAME,
c.name AS COLUMN_NAME
FROM
sys.views v JOIN ...
Posted in SQL Server | No Comments »
Monday, October 20th, 2008
-- Identifies views AND tables used IN their definition
CREATE VIEW INFORMATION_SCHEMA.VIEW_TABLE_USAGE
AS
SELECT DISTINCT
db_name() AS VIEW_CATALOG,
schema_name(v.schema_id) AS VIEW_SCHEMA,
v.name AS VIEW_NAME,
db_name() AS TABLE_CATALOG,
schema_name(t.schema_id) AS TABLE_SCHEMA,
t.name AS TABLE_NAME
FROM
sys.objects t,
sys.views v,
sys.sql_dependencies d
WHERE
d.class < 2
AND d.object_id = v.object_id
AND ...
Posted in SQL Server | No Comments »
Monday, October 20th, 2008
-- Displays views accessable to current user
CREATE VIEW INFORMATION_SCHEMA.VIEWS
AS
SELECT
db_name() AS TABLE_CATALOG,
schema_name(schema_id) AS TABLE_SCHEMA,
name AS TABLE_NAME,
convert(nvarchar(4000),
object_definition(object_id)) AS VIEW_DEFINITION,
convert(varchar(7), CASE with_check_option
WHEN 1 THEN 'CASCADE'
ELSE 'NONE' END) AS CHECK_OPTION,
'NO' ...
Posted in SQL Server | No Comments »
Monday, October 20th, 2008
If you are looking for a way to fetch column descriptions then here are some system views you can refer to.
INFORMATION_SCHEMA.COLUMNS does not have column descriptions
You can query sys.extended_properties
SELECT
[Table Name] = OBJECT_NAME(c.object_id),
[Column Name] = c.name,
[Description] = ex.value
FROM
sys.columns c
LEFT OUTER JOIN
sys.extended_properties ex
ON
ex.major_id = c.object_id
AND ex.minor_id = c.column_id
AND ex.name = 'MS_Description'
WHERE
OBJECTPROPERTY(c.object_id, 'IsMsShipped')=0
-- ...
Posted in SQL Server | No Comments »
Monday, October 20th, 2008
SP_TABLES
EXEC sp_tables @table_type = "'TABLE'";
sysobjects
SELECT * FROM sysobjects
WHERE xtype = 'U';
Sys.tables
SELECT * FROM sys.tables;
Posted in SQL Server | No Comments »
Sunday, October 19th, 2008
The following command gives the list of tables available in the database:
select table_name
from information_schema.tables
where table_type='BASE TABLE'
The following command gives the list of columns of every table in the database, including their data types and widths. You can also use a WHERE clause, if you would like to deal with only ...
Posted in SQL Server | No Comments »
Sunday, October 19th, 2008
The information schema views are defined in a special schema named INFORMATION_SCHEMA. This schema is contained in each database. Each information schema view contains metadata for all data objects stored in that particular database. The following table shows the relationships between the SQL Server names and the SQL standard names.
Tables
To ...
Posted in SQL Server | No Comments »