- SQLServer 2000
- SQLServer 2005
Tips about BI,DWH and so on..
SELECT * FROM sys.tables AS tbl INNER JOIN sys.extended_properties AS p ON p.major_id=tbl.object_id AND p.minor_id=0 AND p.class=1
DECLARE @TABLE_NAME VARCHAR(255) DECLARE @SQL VARCHAR(8000) DECLARE @PROPERTY_NAME VARCHAR(255) SET @PROPERTY_NAME = 'Description' DECLARE C_TABLE CURSOR FOR SELECT name FROM sysobjects WHERE OBJECTPROPERTY(id, N'IsUserTable') = 1 AND status >= 0 ORDER BY name OPEN C_TABLE FETCH NEXT FROM C_TABLE INTO @TABLE_NAME WHILE @@FETCH_STATUS = 0 BEGIN FETCH NEXT FROM C_TABLE INTO @TABLE_NAME PRINT @TABLE_NAME SET @SQL = ' EXEC sys.sp_addextendedproperty @name=N''' + @PROPERTY_NAME + ''', @value=N'''' , @level0type=N''SCHEMA'', @level0name=N''dbo'', @level1type=N''TABLE'', @level1name=N''' + @TABLE_NAME + '''' EXEC (@SQL) END CLOSE C_TABLE
Value prompt has the prompt name and the border(------------) in the list. You can remove these from the list with Java script.
<script type="text/javascript"> function go(){ var combo1 = document.getElementsByName("_oLstChoicesC0")[0]; var combo1List = combo1.getElementsByTagName("OPTION"); combo1.removeChild( combo1List.item(1) ); combo1.removeChild( combo1List.item(0) ); } </script>_oLstChoices means a dropdown list. C0 is ID which is set in the property.
<img src="http://www.blogger.com/pat/images/blank.gif" onload="go()" />This is to execute Java script on that report.
You can run DTS package from the client PC by these ways.
CREATE PROCEDURE [ExecuteDTS_TestPackage] AS EXECUTE msdb..sp_start_job @job_name = 'TestPackage' GO
SQLServer have no funtion to count the length of byte like LENB. This SQL provide same function. *TEST:VARCHAR(25)
DATALENGTH(CONVERT(VARCHAR(25), TEST))
You can see the lock status by sp_lock.
EXEC sp_lock GO
Type | Description |
---|---|
DB | Database |
FIL | File |
IDX | Index |
PG | Page |
KEY | Key |
TAB | Table |
EXT | Extent |
RID | Row ID |