بدست آوردن لیست StoredProcedures های غیره سیستمی موجود در Data Base

[SayeyeZohor]

اين لينك يكي از دوستانه

نام تاپیک: بدست آوردن لیست StoredProcedures های غیره سیستمی موجود در Data Base و حذف آنها (http://barnamenevis.org/showthread.php?251505-%D8%A8%D8%AF%D8%B3%D8%AA-%D8%A2%D9%88%D8%B1%D8%AF%D9%86-%D9%84%DB%8C%D8%B3%D8%AA-StoredProcedures-%D9%87%D8%A7%DB%8C-%D8%BA%DB%8C%D8%B1%D9%87-%D8%B3%DB%8C%D8%B3%D8%AA%D9%85%DB%8C-%D9%85%D9%88%D8%AC%D9%88%D8%AF-%D8%AF%D8%B1-Data-Base-%D9%88-%D8%AD%D8%B0%D9%81-%D8%A2%D9%86%D9%87%D8%A7)


بدست آوردن لیست StoredProcedure های غیره سیستمی موجود در Data Base


SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
where objectproperty(object_id(ROUTINE_NAME),'IsMSShippe d') = 0 and objectproperty(object_id(ROUTINE_NAME),'IsProcedur e') = 1
order by ROUTINE_NAME



SELECT Name FROM SysObjects Where XType = 'P' and objectproperty(object_id(name),'IsMSShipped') = 0
Order By Name

[SayeyeZohor]

بدست آوردن لیست Function های غیره سیستمی موجود در Data Base


SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
where objectproperty(object_id(ROUTINE_NAME),'IsMSShippe d') = 0 and ROUTINE_TYPE ='FUNCTION'
order by ROUTINE_NAME




SELECT Name FROM SysObjects Where XType IN (N''FN'', N''IF'', N''TF'') and objectproperty(object_id(name),'IsMSShipped') = 0
Order By Name

[SayeyeZohor]

بدست آوردن لیست VIEW های غیره سیستمی موجود در Data Base


SELECT Name FROM SysObjects Where XType = 'V' and objectproperty(object_id(name),'IsMSShipped') = 0
Order By Name

[SayeyeZohor]

روش هاي بدست آوردن فيلد PrimaryKey جدول :


--Get table primary key using sql query
SELECT column_name
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE OBJECTPROPERTY(OBJECT_ID(constraint_name), 'IsPrimaryKey') = 1
AND table_name = 'Table1'



select *
from sysobjects
where xtype='pk' and
parent_obj in (select id from sysobjects where name='Table1')




SELECT KU.table_name as tablename,column_name as primarykeycolumn
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU
ON TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND
TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME
and ku.table_name='Table1'
ORDER BY KU.TABLE_NAME, KU.ORDINAL_POSITION;










SELECT COLUMN_NAME FROM {DATABASENAME}.INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME LIKE '{TABLENAME}' AND CONSTRAINT_NAME LIKE 'PK%'
--WHERE
--{DATABASENAME} = your database from your server AND
--{TABLENAME} = your table name from which you want to see the primary key.

--NOTE : enter your database name and table name without brackets.

[SayeyeZohor]

روش هاي بدست آوردن فيلد Identity Key جدول :

--Get table identity key using sql query
--Finding IDENTITY columns
SELECT
t.TABLE_NAME
,c.COLUMN_NAME
,c.TABLE_CATALOG
,c.TABLE_SCHEMA
FROM
INFORMATION_SCHEMA.COLUMNS AS c JOIN
INFORMATION_SCHEMA.TABLES AS t
ON t.TABLE_NAME = c.TABLE_NAME
WHERE
COLUMNPROPERTY(OBJECT_ID(c.TABLE_NAME)
,c.COLUMN_NAME,'IsIdentity') = 1 AND
t.TABLE_TYPE = 'Base Table' AND
t.TABLE_NAME NOT LIKE 'dt%' AND
t.TABLE_NAME NOT LIKE 'MS%' AND
t.TABLE_NAME NOT LIKE 'syncobj_%'

select COLUMN_NAME, TABLE_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA = 'dbo'
and COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
order by TABLE_NAME



IF ((SELECT OBJECTPROPERTY( OBJECT_ID(N'Table1'), 'TableHasIdentity')) = 1)
PRINT 'Yes'
ELSE
PRINT 'No'


SELECT
CASE WHEN C.autoval IS NOT NULL THEN
'Identity'
ELSE
'Not Identity' end
--AND
FROM
sysobjects O
INNER JOIN
syscolumns C
ON
O.id = C.id
WHERE
O.NAME = 'Table1'
AND
C.NAME = 'a'


select o.name + '.' + c.name
from syscolumns c, sysobjects o
where c.status & 128 = 128
and o.id = c.id
order by o.name


select table_name + '.' + column_name, table_name, column_name, ordinal_position, data_type
from information_schema.columns
where
table_schema = 'dbo'
and columnproperty(object_id(table_name), column_name,'IsIdentity') = 1
order by table_name


select TABLE_NAME + '.' + COLUMN_NAME, TABLE_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA = 'dbo'
and COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
order by TABLE_NAME




select table_schema + '.' + table_name + '.' + column_name, table_name, column_name, ordinal_position, data_type
from information_schema.columns
where columnproperty(object_id(table_schema + '.' + table_name), column_name,'IsIdentity') = 1
order by table_name


select IDENT_CURRENT('Table1') as IdentValue
select IDENT_seed('Table1') as IdentValue


select IDENT_incr('Table1') as IdentIncrement

[SayeyeZohor]

بدست آوردن فيلد Foreign Key جدول :

--Get table foreign key using sql query

SELECT
FK_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Relationship_Name = C.CONSTRAINT_NAME
--Constraint_Name = C.CONSTRAINT_NAME
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT
i1.TABLE_NAME,
i2.COLUMN_NAME
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE
i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT
ON PT.TABLE_NAME = PK.TABLE_NAME



SELECT
tc.constraint_name, tc.table_name, kcu.column_name,
ccu.table_name AS foreign_table_name,
ccu.column_name AS foreign_column_name
FROM
information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'FOREIGN KEY' AND tc.table_name='backupmediafamily';




CREATE VIEW foreign_keys_view AS
SELECT
tc.table_name, kcu.column_name,
ccu.table_name AS foreign_table_name,
ccu.column_name AS foreign_column_name
FROM
information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage
AS kcu ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage
AS ccu ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'FOREIGN KEY';




select table_name, column_name
from information_schema.key_column_usage
where constraint_name IN (select constraint_name
from information_schema.referential_constraints
where unique_constraint_name = 'TABLE_NAME_pkey')
--Here 'TABLE_NAME_pkey' is the name of the Primary Key referenced by the Foreign Keys.

[SayeyeZohor]

بدست آوردن Description فيلد جدول :

//--description
SELECT *
FROM ::fn_listextendedproperty(NULL, NULL, NULL, NULL, NULL, NULL, NULL)

SELECT *
FROM ::fn_listextendedproperty(default, default, default, default, default, default, default)

CREATE table T1 (id int , name char (20))

EXEC sp_addextendedproperty 'caption', 'Employee ID', 'user', dbo, 'table', 'T1', 'column', id

EXEC sp_addextendedproperty 'caption', 'Employee Name', 'user', dbo, 'table', 'T1', 'column', name

SELECT *
FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', 'T1', 'column', default)




SELECT sysobjects.Name AS ObjectName,
sysobjects.xtype AS ObjectType,
user_name(sysobjects.uid) AS SchemaOwner,
sysproperties.name AS PropertyName,
sysproperties.value AS PropertyValue,
syscolumns.name AS ColumnName,
syscolumns.colid AS Ordinal
FROM sysobjects INNER JOIN sysproperties
ON sysobjects.id = sysproperties.id
LEFT JOIN syscolumns
ON sysproperties.smallid = syscolumns.colid
AND sysproperties.id = syscolumns.id
ORDER BY SchemaOwner, ObjectName, ObjectType, Ordinal







DECLARE @TableName varchar(100)
SELECT @TableName = 'Table1'


-- This will determine if we're using version 9 (2005) of SQL Server, and execute code accordingly

IF CAST(SUBSTRING(CAST(SERVERPROPERTY('productversion ') as varchar),1,1) as int) >= 9
BEGIN
-- This is a SQL 2005 machine
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
AND OBJECT_NAME(c.object_id) = @TableName
ORDER
BY OBJECT_NAME(c.object_id), c.column_id
END
ELSE
BEGIN
-- assume this is a SQL 2000
SELECT
[Table Name] = i_s.TABLE_NAME,
[Column Name] = i_s.COLUMN_NAME,
[Description] = s.value
FROM
INFORMATION_SCHEMA.COLUMNS i_s
LEFT OUTER JOIN
sysproperties s
ON
s.id = OBJECT_ID(i_s.TABLE_SCHEMA+'.'+i_s.TABLE_NAME)
AND s.smallid = i_s.ORDINAL_POSITION
AND s.name = 'MS_Description'
WHERE
OBJECTPROPERTY(OBJECT_ID(i_s.TABLE_SCHEMA+'.'+i_s. TABLE_NAME), 'IsMsShipped')=0
AND i_s.TABLE_NAME = @TableName
ORDER BY
i_s.TABLE_NAME, i_s.ORDINAL_POSITION
END









SELECT
ORDINAL_POSITION
,COLUMN_NAME
,DATA_TYPE
,CHARACTER_MAXIMUM_LENGTH
,IS_NULLABLE
,COLUMN_DEFAULT
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME = 'Table1'
ORDER BY
ORDINAL_POSITION ASC;

[SayeyeZohor]

بدست آوردن اطلاعات جدول :

-----info of tables
sp_help 'dbo.backupmediafamily'

SELECT ORDINAL_POSITION, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Customers'

SELECT CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
WHERE TABLE_NAME = 'Customers'

SELECT * --name, type_desc, is_unique, is_primary_key
FROM sysindexes
WHERE [object_id] = OBJECT_ID('dbo.Customers')





SELECT
o.[name] AS [Table],
c.[name] AS [Column],
t.[name] AS [Data Type],
c.[prec] AS [Precision],
c.[scale] AS [Scale],
c.[isnullable] AS [Nullable]
FROM [dbo].[sysobjects] o
JOIN [dbo].[syscolumns] c
ON c.[id] = o.[id]
JOIN [dbo].[systypes] t
ON c.[xtype] = t.[xtype]
WHERE o.[type] = 'U' --use 'V' for views
ORDER BY
o.[name],
c.[name]